Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Selecting MAX record and getting corresponding data

Status
Not open for further replies.

luvmy2Js

IS-IT--Management
Sep 1, 2010
43
US
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!!


 

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)
 
Code:
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'
 
just the row with latest overall date in the table?
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top