March 8, 2012 at 2:28 pm
I am having problems getting down to a single record with this query. I have a sample of six records that I need narrowed down to a single entry per number. The SELECT statement works provided there are not multiple entries for the same number on the same day. I come from a VFP background and did not have to contend with the problems of adding every field in the SELECT list to the group by list. I am not sure how to accomplish this in SQL.
number cashdate cashamt
DUNN 2007-11-19 00:00:00.000 13.70
DUNN 2007-11-19 00:00:00.000 154.00
DUNN 2007-11-19 00:00:00.000 199.30
DUNN 2007-11-19 00:00:00.000 440.00
DUNN 2007-11-19 00:00:00.000 1242.00
DUNN 2007-11-19 00:00:00.000 1441.00
SELECT temp.number AS number2, MAX(temp.cashdate) AS cashdate,temp.cashamt
FROM (SELECT number, MAX(cashdate) AS cashdate
FROM arcashha GROUP BY number) AS temp1
INNER JOIN arcashha AS temp ON temp.number = temp1.number AND temp.cashdate = temp1.cashdate
WHERE temp.number = 'DUNN'
GROUP BY temp.NUMBER, temp.CASHAMT
ORDER BY temp.number
March 8, 2012 at 2:32 pm
What is your expected result? Are you looking for the min amount or max or sum or avg?
In case you're looking for the "first" amount, we'd need to know what column could be used to identify this "first" value.
March 8, 2012 at 2:33 pm
I agree with Lutz, it is a little unclear what you want here. You have max date in your query but you have all the same dates. Do you want the max amount instead?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply