Returning the first row for each group
-
Currently I have a stored procedure that queries data, loads it into a cursor, loops through it and pulls out the data I need and then returns it in a new table.
I'm looking for a way to do this in one query or at least in a way that gets rid of the cursor....it's just too slow.
This is the test query and the result set:
select groupID, groupValue, dateCreated
from tbl1
order by groupID ASC, dateCreated DESC;
groupID groupValue dateCreated
-------- ---------- -----------------------
1 42 2006-03-02 14:43:54.070
1 42 2006-03-02 14:43:25.510
1 42 2006-02-23 14:24:11.110
1 43 2006-02-09 22:17:17.973
1 43 2006-02-09 22:01:42.160
1 43 2006-02-09 22:01:23.287
1 43 2006-02-09 21:58:48.733
1 43 2006-02-09 21:56:50.840
1 43 2006-02-09 21:36:10.040
1 43 2006-02-09 21:08:32.510
1 43 2005-11-15 14:15:10.570
1 3 1970-01-01 00:00:00.000
3 12 2005-11-15 14:13:34.023
4 34 2005-11-15 14:16:29.463
4 43 2005-10-17 13:25:36.000
5 7 2006-02-24 18:31:59.230
5 7 2006-02-24 18:31:45.807
5 7 2006-02-24 18:31:41.680
5 7 2006-02-24 18:01:04.977
5 7 2006-02-23 19:44:20.003
5 43 2006-02-23 19:43:13.643
What I need it to do is pull the first groupID from the result set so it returns this:
groupID groupValue dateCreated
-------- ---------- -----------------------
1 42 2006-03-02 14:43:54.070
3 12 2005-11-15 14:13:34.023
4 34 2005-11-15 14:16:29.463
5 7 2006-02-24 18:31:59.230
Any ideas on how to do this either in a single query or at least efficiently?
-
Join to a derived table that gives you Max(Date) per group to join to:
select tbl1.groupID, tbl1.groupValue, tbl1.dateCreated
from tbl1
Inner Join
(
Select GroupID, Max(DateCreated) As MaxCreatedPerGroup
From tbl1
Group By GroupID
) dt
On (dt.GroupID = tbl1.GroupID And
dt.MaxCreatedPerGroup = tbl1.DateCreated);
-
Use this simple Query:-
select groupID, groupValue, max(dateCreated)
from tbl1
group by groupID, groupValue
order by groupID ASC, dateCreated DESC
Regards
Amit Gupta
-
That "simple" query is too simple:
group by groupID, groupValue
You can't group on GroupValue, if the requirement is to eliminate all but 1 of the GroupValues for each GroupID.
-
PW: is there any need for the following...
dt.MaxCreatedPerGroup = tbl1.DateCreated
...in your ON clause? As if the inner SELECT is grouping by GroupID, meaning it should only bring back one row for each groupid.
Tryst
-
DOH!
Joining to the subquery worked. Can't tell you how many times I have done that in the past for similar queries...
Thanks!
-
HI,
Sorry!!
I don't see the GroupValue i think for a particular groupid ,it contains same kind of value.
You can Try this one::-
select groupID, (select groupValue from tbl1 where dateCreated=(select max(dateCreated) from tbl1)'groupValue', max(dateCreated)
from tbl1
group by groupID, groupValue
order by groupID ASC, dateCreated DESC
Regards
Amit Gupta
-
>>PW: is there any need for the following...
>>dt.MaxCreatedPerGroup = tbl1.DateCreated
It doesn't work without that. The purpose of the derived table is to find the most recent date per GroupID. If you don't join to the most recent date then you don't satisfy the requirements to bring back latest row per GroupID.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply