SQL Server 2012 - MAX() Function

Question

I am pulling data from 3 separate employee related tables, all joined on SSN. Because some employees have transferred within the company multiple times they have multiple hire/separation dates in the system, so when I pull from that table with the hire dates, it duplicates the row by the number of hire/separation dates in the system. Here's the data sample as it's pulled:

SSN         Name        Pay_Date    Hire_Date
123456789   John Doe    5/1/2012    1/1/2001
123456789   John Doe    5/1/2012    2/5/2005
123456789   John Doe    5/1/2012    3/1/2012
123456789   John Doe    5/15/2012   1/1/2001
123456789   John Doe    5/15/2012   2/5/2005
123456789   John Doe    5/15/2012   3/1/2012
123456789   John Doe    5/29/2012   1/1/2001
123456789   John Doe    5/29/2012   2/5/2005
123456789   John Doe    5/29/2012   3/1/2012

The query:

SELECT

SSN, Name, Pay_Date, Hire_Date

FROM Personnel as PER

LEFT JOIN Payroll as PAY on PER.SSN = PAY.SSN

LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN

ORDER BY Pay_Date(DESC)

To eliminate the rows with hire dates 1/1/2001 and 2/5/2005, I tried using the MAX function as follows with no luck. I tried using a variety of the examples posted on previous topics related to MAX but nothing is working.

SELECT

SSN, Name, Pay_Date, MAX(Hire_Date)

FROM Personnel as PER

LEFT JOIN Payroll as PAY on PER.SSN = PAY.SSN

LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN

GROUP BY SSN

ORDER BY Pay_Date(DESC)

Again, I just need the row with the latest hire date. So the outcome should look like

SSN         Name        Pay_Date    Hire_Date
123456789   John Doe    5/1/2012    3/1/2012
123456789   John Doe    5/15/2012   3/1/2012
123456789   John Doe    5/29/2012   3/1/2012

Can you show the base tables for your example ?

Ndech
– Ndech

2014-09-03 17:43:33 +00:00
Commented Sep 3, 2014 at 17:43 — Ndech
– Ndech, Commented Sep 3, 2014 at 17:43

Giannis Paraskevopoulos · Accepted Answer · 2014-09-04 06:49:47Z

2

;WITH MyCTE AS
(
    SELECT SSN, 
                   Name, 
                   Pay_Date, 
                   Hire_Date,
                   ROW_NUMBER() OVER(PARTITION BY SSN, Name, Pay_Date ORDER BY Hire_Date DESC) AS rn
    FROM     Personnel as PER
    LEFT JOIN Payroll as PAY on  PER.SSN = PAY.SSN
    LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN
)
SELECT * 
FROM    MyCTE
WHERE rn = 1

SQL Fiddle Demo Powered by Lamak

edited Sep 4, 2014 at 6:49

answered Sep 3, 2014 at 17:47

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lamak Over a year ago

+1. You have a typo in your code (you wrote ORDE BY, it's missing the R). And here is a sqlffidle with a demo of your code

Giannis Paraskevopoulos Over a year ago

I edited my answer. I am writing from my phone so couldn't really use fiddle. May i use your sample with your permission?

Joseph Over a year ago

@GiannisParaskevopoulos yes you can use my sample.

Joseph Over a year ago

@GiannisParaskevopoulos it is still repeating. I adjusted my join type, which in this case shouldn't matter, however it is continuing to repeat. Does the number of fields I have matter? Because in reality there are 39 per row.

Giannis Paraskevopoulos Over a year ago

Try to create some sample data with all the fields in sql fiddle and the we ll see how we can help.

|

Mike Miller · Accepted Answer · 2014-09-03 17:42:45Z

0

Is it the latest Pay date and hire date? if so this should work.

SELECT

SSN, Name, MAX(Pay_Date), MAX(Hire_Date)

FROM Personnel as PER

LEFT JOIN Payroll as PAY on PER.SSN = PAY.SSN

LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN

GROUP BY SSN, Name

ORDER BY Pay_Date(DESC)

answered Sep 3, 2014 at 17:42

Mike Miller

16.6k1 gold badge22 silver badges27 bronze badges

1 Comment

Joseph Over a year ago

it's only the latest hire date.

Tab Alleman · Accepted Answer · 2014-09-03 17:45:57Z

0

To get the row with the latest hire date:

SELECT
SSN, Name, Pay_Date, Hire_Date
FROM Personnel as PER
LEFT JOIN Payroll as PAY on PER.SSN = PAY.SSN
LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN
WHERE ROW_NUMBER() OVER (PARTITION BY SSN, ORDER BY Hire_Date DESC)=1
ORDER BY Pay_Date DESC

answered Sep 3, 2014 at 17:45

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

2 Comments

Giannis Paraskevopoulos Over a year ago

You may not use ROW_NUMBER in a WHERE clause directly.

Tab Alleman Over a year ago

Really? Good to know...thought I could take a short cut from the CTE that way. I also didn't notice that the OP used the same SSN on all rows of his sample data, so your partition by is more correct than mine. I think I will delete this answer shortly.

Ryan B. · Accepted Answer · 2014-09-03 21:20:29Z

0

Aggregate functions in this case are problematic because MAX() data for different columns can come from different rows in your set.

Instead, use window functions to discover what row in each group of SSNs has the highest (most recent) Hire_Date and then select your results from that subset.

SELECT SSN, Name, Pay_Date, Hire_Date
FROM
    (
    SELECT

        SSN
        , Name
        , Pay_Date
        , Hire_Date
        , ROW_NUMBER() over (PARTITION BY SSN ORDER BY Hire_Date DESC) rn
    FROM
        Personnel as PER

        LEFT JOIN Payroll as PAY on PER.SSN = PAY.SSN

        LEFT JOIN HumanResources as HR on PER.SSN = HR.SSN
    ) x

WHERE 
    x.rn = 1

ORDER BY Pay_Date DESC

edited Sep 3, 2014 at 21:20

answered Sep 3, 2014 at 17:56

Ryan B.

3,6852 gold badges22 silver badges28 bronze badges

2 Comments

Joseph Over a year ago

it keeps giving me the incorrect syntax near 'WHERE'. When i hover over the error it says it's expecting AS,ID or QUOTED_ID. But those make no sense and when I try them they don't work anway

Ryan B. Over a year ago

Sorry, a few syntax errors. I added the alias 'x' and removed the parentheses from around (DESC). Actually used the sqlfiddle from @Lamak to test and it should be good now.

Collectives™ on Stack Overflow

SQL Server 2012 - MAX() Function

4 Answers 4

9 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related