I have a problem where I need to get the earliest date value from a table grouped by a column, but sequentially grouped.
Here is a sample table:
if object_id('tempdb..#tmp') is NOT null
DROP TABLE #tmp
CREATE TABLE #tmp
(
UserID BIGINT NOT NULL,
JobCodeID BIGINT NOT NULL,
LastEffectiveDate DATETIME NOT NULL
)
INSERT INTO #tmp VALUES ( 1, 5, '1/1/2010')
INSERT INTO #tmp VALUES ( 1, 5, '1/2/2010')
INSERT INTO #tmp VALUES ( 1, 6, '1/3/2010')
INSERT INTO #tmp VALUES ( 1, 5, '1/4/2010')
INSERT INTO #tmp VALUES ( 1, 1, '1/5/2010')
INSERT INTO #tmp VALUES ( 1, 1, '1/6/2010')
SELECT JobCodeID, MIN(LastEffectiveDate)
FROM #tmp
WHERE UserID = 1
GROUP BY JobCodeID
DROP TABLE [#tmp]
This query will return 3 rows, with the min value.
1 2010-01-05 00:00:00.000
5 2010-01-01 00:00:00.000
6 2010-01-03 00:00:00.000
What I am looking for is for the group to be sequential and return more than one JobCodeID, like this:
5 2010-01-01 00:00:00.000
6 2010-01-03 00:00:00.000
5 2010-01-04 00:00:00.000
1 2010-01-05 00:00:00.000
Is this possible without a cursor?