SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 Other Topics
 Simple MAX function not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bgodwin
Starting Member

USA
3 Posts

Posted - 07/25/2007 :  10:45:50  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
324 Posts

Posted - 07/25/2007 :  10:57:28  Show Profile  Reply with Quote
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

Edited by - cardgunner on 07/25/2007 11:02:12
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/25/2007 :  11:02:59  Show Profile  Reply with Quote

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




CODO ERGO SUM
Go to Top of Page

bgodwin
Starting Member

USA
3 Posts

Posted - 07/25/2007 :  15:58:12  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/25/2007 :  16:40:22  Show Profile  Visit tkizer's Homepage  Reply with Quote
It is called a derived table.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/25/2007 :  16:45:54  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 07/25/2007 16:51:01
Go to Top of Page

bgodwin
Starting Member

USA
3 Posts

Posted - 07/25/2007 :  18:04:43  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000