Maximum Rate per Person - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Maximum Rate per Person

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-10-05 : 11:29:07

I would like to get a Maximum Rate per Employee Number. I have written the following:

SELECT
Sheet1.EmplNum,
Sheet1.Rate,
Sheet1.HireDate,
Sheet1.PayType,
Sheet1.RateEvent,
Sheet1.Begin_Date,
Sheet1.employee

FROM Sheet1
Inner Join
(Select
Max(Sheet1.Rate AS "MaxRate"
AND
Sheet1.EmplNum Count(*))

GROUP BY
Sheet1.EmplNum

ON Sheet1.EmplNum=Sheet1.EmplNum
AND
Sheet1.Rate=Sheet1.MaxRate

Any assistance you could provice would be appreciated.

Thank you,

Mary

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 11:37:39

It would be nice to have the table structure, example data and wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-10-05 : 11:46:14

The table structure consists of the following columns:

EmplNum,
Rate,
HireDate,
PayType,
RateEvent,
Begin_Date,
employee

The emplnum (employee number) is repeated in the table many times because the pay rate (Rate Column) has changed several times per employee number. What I need to find is the maximum pay rate per employee number.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-05 : 11:54:35

This?

SELECT
s1.EmplNum,
s1.Rate,
s1.HireDate,
s1.PayType,
s1.RateEvent,
s1.Begin_Date,
s1.employee

FROM Sheet1 s1

Inner Join
(
Select
Max(Rate) AS MaxRate,
EmplNum
FROM Sheet1
GROUP BY EmplNum
)dt
ON  s1.EmplNum=dt.EmplNum
AND s1.Rate=dt.MaxRate

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 12:24:07

if 2005 then


SELECT s1.EmplNum,
s1.Rate,
s1.HireDate,
s1.PayType,
s1.RateEvent,
s1.Begin_Date,
s1.employee
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY EmplNum ORDER BY Rate DESC) AS Seq
FROM Sheet1
)s1
WHERE s1.Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-10-05 : 12:24:25

This works but I have a couple of questions just so I can understand. I see the error I made in syntax, but in the FROM Line:

Sheet1 s1
Did this create an alias for Sheet1?

Later in the Select statement at the end )dt
What is dt? Is this an alias for the sub query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-05 : 12:26:51

quote:
Originally posted by meberg66219

This works but I have a couple of questions just so I can understand. I see the error I made in syntax, but in the FROM Line:

Sheet1 s1
Did this create an alias for Sheet1?

Later in the Select statement at the end )dt
What is dt? Is this an alias for the sub query?

yeah. Both are aliases used one for Sheet1 table and other for derived table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-10-05 : 14:03:05

Thank you very much for the assistance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources