Simple MAX function not working

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

Other Forums

Other Topics

Simple MAX function not working

Author

Topic

bgodwin
Starting Member

3 Posts

Posted - 2007-07-25 : 10:45:50

This is my first post and I'm very grateful if someone would be so kind to end my misery.

This HAS to be simple and I'm not sure WHY SQL Server wont do this.

Pay rate history file, I want to know the latest payrate for a person. i.e.
emp # eff date pay rate
4570 1/1/2007 10.20
4570 5/2/2006 9.25

The result should be emp #4570 and their 2007 rate of 10.20.

I've googled until I'm nearly blind. I've read so many forums that give a lot of T-sql solutions, I just want to do a simply select query for this.

Why wouldn't this work:

select emp, date, payrate
from prratehist
where emp = 4570 and date in (select max(date) from prratehist)

I get 0 results

I've also tried:

select EMPLOYEE, BEG_DATE, PAY_RATE
from lawson.PRRATEHIST (NOLOCK)
WHERE
EMPLOYEE = 4570
GROUP BY EMPLOYEE, BEG_DATE, PAY_RATE
HAVING BEG_DATE = MAX(BEG_DATE)

Which gives me both rows when I only want the one. I've tried other things to no avail and I cannot swallow that this should be at all hard.

My workaround is to put table data in Excel and sort desc by date, then do a adv filter unique to hide all but the first record for each employee...then vlookup from there - misery!!

Please help me, it would be so appreciated.

Beverly Godwin

cardgunner

326 Posts

Posted - 2007-07-25 : 10:57:28

I'm hoping I can help but I'm kinda new at this myself.

When I need to do a max satement I would have to structure it like


select hist.emp, tmp.date, hist.rate
from hist
left join (max(date)as date, emp 
           from hist 
           group by emp)tmp
on hist.emp=tmp.emp AND hist.date=tmp.date

Card Gunner

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 11:02:59

[code]
select
a.emp,
a.date,
a.payrate
from
prratehist a
join
(
select
b1.emp,
date = max(b1.date)
from
prratehist b1
group by
b1.emp
) b
on a.emp = b.emp and a.date = b.date

[/code]

CODO ERGO SUM

bgodwin
Starting Member

3 Posts

Posted - 2007-07-25 : 15:58:12

THANKS very much to both of you

I'm using SQL Server and for some reason Michael's solution did not work but Cardgunner's did.

I'm sad that there was not a solution that was more on what I've seen before (using having or subqueries or self joins)...

I'm pretty good with SQL (so I thought) and have never seen that you could join a table that is created on the fly like that. I'm bewildered and now considering that it is time for me to retake the SQL class that I had years ago. What is this type of query called?

EYE OPENER!!

Thanks to this query working, I just learned that there could be 2 diff pay rates with SAME eff date, so now I have to look at max object id also - not to take up too much of your time, but is there a way that I can tell this query that when there are 2 results, I need it narrowed down by the one with the latest object id?
I can't look at just object id, because a lot of time it is just 0 for several of the employees records.

Beverly Godwin

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 16:40:22

It is called a derived table.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-25 : 16:45:54

quote:
Originally posted by bgodwin
...I'm using SQL Server and for some reason Michael's solution did not work but Cardgunner's did...

I find it hard to believe that Cardgunner's code worked, since it isn't valid SQL syntax; there is no SELECT at the beginning of the derived table.

CODO ERGO SUM

bgodwin
Starting Member

3 Posts

Posted - 2007-07-25 : 18:04:43

SORRY, I MAY HAVE GOTTEN THE NAMES MIXED UP...THIS IS THE ONE THAT WORKED, WHICH I BELIEVE CAME FROM MICHAEL.

select A.EMPLOYEE, A.PAY_RATE, A.BEG_DATE
from lawson.PRRATEHIST A(NOLOCK)
JOIN
(SELECT B1.EMPLOYEE, DATE = MAX(B1.BEG_DATE)
FROM lawson.PRRATEHIST B1 (NOLOCK)
GROUP BY B1.EMPLOYEE) B
ON A.EMPLOYEE = B.EMPLOYEE AND A.BEG_DATE = B.DATE

Beverly Godwin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources