October 23, 2006 at 9:24 pm
I have a table that is sorted by ID and date, what I want is the maximum date row returned off each grouping.
eg.
ID SaleID SaleDate Cust
100 123 23-04-2006 JJ Inc
100 45784 12-01-2006 Barbie
100 78 01-12-2005 Microflock
110 99 09-04-2006 ZZ Pty Ltd
110 100 23-11-2005 ABC
Outcome of results
ID SaleId SaleDate Cust
100 123 23-04-2006 JJ Inc
110 99 09-04-2006 ZZ Pty Ltd
In Access I would use something like (but I need this in SQL server 2005)
SELECT ID, First(SaleId), Max(SaleDate), First(Cust) From Sales Group By ID Order By SaleDate DESC
Help appreciated
October 24, 2006 at 1:06 am
You have posted in the SQL Server 2000 forum so I will give you an answer for SQLS2k - it should work the same in 2005, but SQLS2005 may have other ways of getting at the same result which I'm not aware of.
SELECT s.ID, s.SaleID, s.SaleDate, s.Cust
FROM Sales s
JOIN (select s2.[id] as sid, MAX(s2.SaleDate) as maxdate
from Sales s2
group by s2.[id]) as Q
ON Q.sid = s.[id] AND Q.maxdate = s.SaleDate
This solution will have problems if you can have several rows with the same ID and date... it will return all rows with the same date; if you have time portion included in your data, this will not happen often - if you only store the date without time (i.e. time is 00:00:00), it could be a serious problem.
In case of several rows with the same id and date, which one is considered "maximum"?
October 24, 2006 at 1:46 am
I can have several fields that are not to be aggregated, only the ID field is to be grouped and the date field is to be max(). It is unlikely that there will be two dates that are the same for any 'Id'.
You solution looks promising - I'll give it a whirl. thanks
October 25, 2006 at 12:55 pm
If i read you right this should solve the problem as well
select ID,max(SaleDate) from #tab_test group by ID
--replace #tab_test by your table.
October 25, 2006 at 1:01 pm
Sorry . mIssed out on the other columns in the select
select x.ID,x.MDate,y.SaleID,y.Cust from (
select ID,max(SaleDate) MDate from #tab_test group by ID)x, #tab_test y
where x.ID=y.ID and x.MDate = y.SaleDate
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply