September 3, 2009 at 9:06 am
Im having trouble writing a query.
i have a table with 3 columns
create table Tab1
(
ItemId int,
GroupID int,
endDate datetime
)
a sample of the data is as follows:
ItemID GroupIDEndDate
1000 1093 2008-09-01
2333 1093 2006-09-01
4933 3234 2007-09-01
2093 2345 2008-09-01
1000 2343 2007-09-01
I need to find the itemid and groupid with highest endDate for each item
I have been running a query like
select distinct itemid,groupid,max(enddate)
from tab1
where itemid in
(
1000
,2333
,4933
,2093
)
group by itemid,groupid
unfortunately such a query returns 2008-09-01 for all records returned, even though i know all itemID's dont have that date.
Can anyone help me wirte this query?
September 3, 2009 at 9:18 am
Sorry, what I had assumed too much. Are you sure your query isn't working?
I did this just to make sure and it worked fine.
create table #Tab1
(
ItemId int,
GroupID int,
endDate datetime
)
INSERT INTO #Tab1
SELECT 1000, 1093, '09/01/2008'
UNION ALL
SELECT 2333, 1093, '09/01/2006'
UNION ALL
SELECT 4933, 3234, '09/01/2007'
UNION ALL
SELECT 2093, 2345, '09/01/2008'
UNION ALL
SELECT 1000, 2343, '09/01/2007'
select distinct itemid,groupid,max(enddate)
from #tab1
where itemid in
(
1000
,2333
,4933
,2093
)
group by itemid,groupid
RESULTS:
100010932008-09-01 00:00:00.000
233310932006-09-01 00:00:00.000
100023432007-09-01 00:00:00.000
209323452008-09-01 00:00:00.000
493332342007-09-01 00:00:00.000
September 3, 2009 at 9:23 am
But if you are only wanting 1 row per itemID you could do something like this:
SELECT * FROM #tab1 a
WHERE endDate = (SELECT MAX(enddate) FROM #tab1 b WHERE a.ItemId = b.itemid)
September 6, 2009 at 6:55 pm
This will work as well
;with cte as
(SELECT *,ROW_NUMBER() OVER (PARTITION BY itemID ORDER BY endDate DESC) as rowID
FROM #tab1 a
)
SELECT * FROM cte WHERE rowID = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2009 at 7:56 pm
For larger tables, with a useful index on endDate, this may be even faster than the ROW_NUMBER version:
SELECT TOP (1)
T1.endDate,
C.GroupID,
C.ItemId
FROM #Tab1 T1
JOIN (
SELECT T2.endDate,
T2.ItemId,
T2.GroupID
FROM #Tab1 T2
) C
ON C.endDate = T1.endDate
ORDER BY
T1.endDate;
That's written freehand, so no guarantees :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 6, 2009 at 9:12 pm
Winston, I think we need you to clarify something about your requirements. I noticed that you had one itemID that was associated with two different groupIDs.
My solution would return a row for each itemID
Paul's solution would return a single row.
Which is the result you want?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2009 at 10:35 pm
Bob,
Good point - though you can add 'WITH TIES' to my TOP (1)...
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply