Simple Query Help
-
Hi,
I need some help with a query. I have data that resembles the following:
Row 1 = Group ID: 1, Task ID: 2, Create DateTime: 04/30/03 10:00
Row 2 = Group ID: 1, Task ID: 4, Create DateTime: 04/30/03 10:15
Row 3 = Group ID: 2, Task ID: 9, Create DateTime: 04/30/03 09:00
Row 4 = Group ID: 2, Task ID: 6, Create DateTime: 04/30/03 09:05
I need to retrieve the task id with the earliest create datetime for its group id. So in this example, my desired resultset would be:
Row 1 = Group ID: 1, Task ID: 2
Row 2 = Group ID: 2, Task ID: 9
I've tried various combinations using the Min function and Group By or Having clauses but am not having much luck. Any help would be greatly appreciated.
Thanks,
Steve
-
Not the best but try :
Create Table Test(GroupId Int,TaskId Int,Created smalldatetime)
Go
Insert Test Values(1,2,'04/30/03 10:00')
Insert Test Values(1,4,'04/30/03 10:15')
Insert Test Values(2,9,'04/30/03 09:00')
Insert Test Values(2,6,'04/30/03 09:05')
Go
Select a.GroupId,
(Select Top 1 t.TaskId
From Test as t
Where t.GroupId=a.GroupId And t.Created=a.Created),
a.Created
From
(Select GroupId,Min(Created)
From Test
Group by GroupId) as A(GroupId,Created)
Go
-
There are several ways you can do this. Building on 5409045121009's setup here is another with a bit less subquerying.
SELECT
a.GroupID,
a.TaskID
FROM
Test a
INNER JOIN
(SELECT iq.GroupID, Min(iq.Created) AS Created FROM Test iq GROUP BY iq.GroupID) AS b
ON
a.GroupID = b.GroupID AND
a.Created = b.CreatedNote however, in this one if two TaskIDs have the exact same time in their GroupID then you will get both.
Edited by - antares686 on 05/01/2003 04:12:14 AM
-
I dont have query analyzer handy at the moment so did not test the working of the
following query yet. you can try this in the
meanwhile.
select distinct groupid,TaskId,Time
from Tasks a
where exists (
select TOP 1 Taskid from Tasks
where groupid = a.groupid
order by time)
-
Personally do not like distinct.
Results:
groupid TaskId Created
----------- ----------- ------------------------------------------------------
1 2 2003-04-30 10:00:00
1 4 2003-04-30 10:15:00
2 6 2003-04-30 09:05:00
2 9 2003-04-30 09:00:00
-
Much Thanks for all the help from everyone!!
Steve
-
SET NOCOUNT ON
DECLARE @tbl TABLE (GroupId INT,TaskId INT,Created SMALLDATETIME)
INSERT @tbl VALUES(1,2,'04/30/03 10:00')
INSERT @tbl VALUES(1,4,'04/30/03 10:15')
INSERT @tbl VALUES(2,9,'04/30/03 09:00')
INSERT @tbl VALUES(2,6,'04/30/03 09:05')
SELECT
A.GroupId,
A.TaskId
FROM
@tbl A INNER JOIN
@tbl B
ON A.GroupId = B.GroupId INNER JOIN
@tbl C
ON A.Created = C.Created
GROUP BY
A.GroupId,
A.Created,
A.TaskId
HAVING
A.Created = MIN(B.Created)
AND
A.TaskId = MIN(C.TaskId)
Edited by - macrotrenz on 05/07/2003 2:44:16 PM
Edited by - macrotrenz on 05/07/2003 2:44:37 PM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply