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 Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select a distinct row based on max value 1

Status
Not open for further replies.

czab

Technical User
Aug 29, 2004
22
US
I need to select only the data in the rows with the most recent date.

Sample data
ITEM DATE ESTIMATE
0010 20070320 100.00
0010 20070321 65.00
0010 20070322 80.00
0020 20070316 75.00
0020 20070318 20.00
0030 20070316 30.00
0040 20070316 40.00


Desired result:

0010 20070322 80.00
0020 20070318 20.00
0030 20070316 30.00
0040 20070316 40.00
 
This works in Oracle, so might be ANSI-SQL compliant

select * from table where item || date in (select item || max(date) from table group by item)

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
Thanks for your reply johnherman.

My data is a little more detailed than what I showed above, but I applied your suggestion to my query and came out with this code:
Code:
SELECT
  CJBNO AS JOB,
  CJCDI AS COSTDISTR,
  QPDATE AS FRDATE,
  QCUPC AS PCOMPLETE,
  QESHM AS ESTIMATE
FROM
  JCVQYD85
WHERE
 (CJBNO || CJCDI || CRCTY || QPDATE in 
  (SELECT CJBNO || CJCDI || CRCTY || max(QPDATE) from JCVQYD85 group by CJBNO, CJCDI, CRCTY, QPDATE))
  AND  (CCONO  =  @variable('1. Company')
  AND   CJBNO  =  @variable('2. Job')
  AND   QCSTY  =  'L')

Unfortunately, this modification only made the query take 8 minutes longer to run and I still got the same results. The output had the same number of lines as the input and didn't select only the records with the highest date value.

Can you see where I may have gone wrong?
 
Try removing the QPDATE from the GROUP BY

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
I removed QPDATE from the GROUP BY statement, but still got the same result. 8 extra minutes to run and the same number of lines of output.

:-(
 
Last shot. Try changing the group by to
CJBNO || CJCDI || CRCTY

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright
 
I can't even run the query if I do that. It results in an error message:
SQL0122 - Column CJBNO or expression in SELECT list not valid.State: HY000

Thanks for your persistance though.

I've found something similar to what I'm trying to do in this topic:
The difference is that it joins two tables. The logic looks similar to what you're suggesting though. I'm not sure why your suggestion isn't working for me.
 
Sample data
ITEM DATE ESTIMATE

SELECT A.ITEM, A.DATE, A.ESTIMATE
FROM JCVQYD85 A INNER JOIN (
SELECT ITEM, MAX(DATE) MaxDate FROM JCVQYD85 GROUP BY ITEM
) M ON A.ITEM = M.ITEM AND A.DATE = M.MaxDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Thanks PHV. I just got a chance to try your suggestion this morning. It worked like a charm. I got exactly the results I wanted. I really appreciate your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top