September 13, 2011 at 2:03 pm
This should be easy but I am running into all cans of trouble trying to figure this one out. I need to be able to select records from a table based on the min(date) field. Lets say I have the following fields
Id, SourceID, Date. The same Id and source can be listed multiple time in the table or the same id and a differenct source can be listed in the table.
I just need to be able to return the record for the id with the earliest date.
Help please!!
September 13, 2011 at 2:10 pm
stevenplee (9/13/2011)
This should be easy but I am running into all cans of trouble trying to figure this one out. I need to be able to select records from a table based on the min(date) field. Lets say I have the following fieldsId, SourceID, Date. The same Id and source can be listed multiple time in the table or the same id and a differenct source can be listed in the table.
I just need to be able to return the record for the id with the earliest date.
Help please!!
not sure if this is the best way to do it (and I am responding with the expecation that I might learn something here) but ONE way to do it:
select id,sourceid,date from tablename where date in (select min(date) from tablename)
September 13, 2011 at 6:54 pm
There are many ways to express this requirement in SQL (some would say too many - and not one that is a straightforward as the natural language form of the question!)
Here's one:
DECLARE @Example TABLE
(
idINTEGER NOT NULL,
source_idINTEGER NOT NULL,
the_dateDATE NOT NULL
)
INSERT @Example
(id, source_id, the_date)
VALUES
(1, 1, '2008-01-01'),
(2, 2, '2009-01-01'),
(1, 1, '2010-01-01'),
(2, 2, '2011-01-01'),
(3, 1, '2008-07-01'),
(4, 2, '2009-07-01'),
(5, 3, '2008-07-01'),
(6, 4, '2009-07-01')
-- Record with the earliest date
SELECT
e.id,
e.source_id,
e.the_date
FROM @Example AS e
WHERE
e.the_date =
(
SELECT TOP (1)
e2.the_date
FROM @Example AS e2
ORDER BY
e2.the_date ASC
)
-- Record with the earliest date per source_id
SELECT
e.id,
e.source_id,
e.the_date
FROM @Example AS e
WHERE
e.the_date =
(
SELECT TOP (1)
e2.the_date
FROM @Example AS e2
WHERE
e2.source_id = e.source_id
ORDER BY
e2.the_date ASC
)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2011 at 9:55 am
Thanks to all who has responded. I appreciate your input!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply