Selecting MAX record and getting corresponding data

[luvmy2Js]

I have the below data and want to get the max date with all corresponding data:

sapid startdate number cstctr job
500032 02/10/2010 30001450 AR01000008 10000549
500032 04/01/2009 39000345 AR01000003 10000549
500032 09/16/2009 30001380 AR01000003 10000549
500032 09/29/2008 39000345 AR01000003 00000000
500032 12/01/2006 39000345 AR01000003 00000000

IF I do a "max" on all fields it returns:
500032 02/10/2010 39000345 AR01000008 10000549
which is the "max" on all fields, itdoesn't return the corresponding data for date "02/10/2010" (since that is the max record. If I only do a "max" on only the date field I have to group by the others therefore returning all data?

How can I write the below code:
SELECT
sapid, startdate, number, cstctr, job
from cust
where sapid = '500032'

To get these results based on the data provided above:
sapid startdate number cstctr job
500032 02/10/2010 30001450 AR01000008 10000549

Thanks a bunch!!

 

[dhulbert]

So which field you you want to spilt the data by and which field is the "max" on?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[PDreyer]

SELECT sapid, startdate, number, cstctr, job 
from cust c
join (select max_startdate=max(startdate) from cust)m
  on c.startdate=max_startdate
where sapid = '500032'

 

[r937]

just the row with latest overall date in the table?

SELECT TOP 1
       sapid
     , startdate
     , number
     , cstctr
     , job 
  FROM cust
 WHERE sapid = '500032' 
ORDER
    BY startdate DESC

or did you mean the row with the latest date per sapid?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[luvmy2Js]

Thanks a bunch I figured it out!!!