Finding first record w/ Min date value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding first record w/ Min date value

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 12:28:40

I have a stored proc in which I need to find the Minimum date value of my records. And if there are multiple records with that minimum date, I need to return only the first one created (i guess by looking at the record id).

Can someone help me with this query? My date field may also be a NULL value. In which, if their are multiple NULL value fields, I just want to show the first one created.

Thanks,
David

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 12:32:41

SELECT MIN(DateColumn)
FROM Table1

This will get the minimum date value. It gets only one row returned. Is that what you are looking for?

Tara

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 12:38:42

That gives me the minium date value in the date column. I can then use that value in my where clause to search for records with that date value. However, it may return multiple records. If this is the case, I need to return the first of these records based on their record id.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 12:45:26

Perhaps if you gave us your table definition (CREATE TABLE statement please), sample data (INSERT INTO statements), and expectd result set of the sample data, then we would be able to provide a solution for you. As it stands now, I don't understand what you are asking for. But maybe this is what you want instead:

SELECT MIN(DateColumn)
FROM Table1
GROUP BY RecordID

Tara

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 12:55:46

My goal is to query my table and return the first record (based on the resource_id) with the earliest resource_date value.

CREATE PROCEDURE Resources_Get (@iMyID int, @iShowAll int)

AS
Declare @dEarliestDate datetime
SELECT @dEarliestDate = Min(resource_date) From tbl_resources WHERE resource_approved = 0

SELECT
resource_id as ResID,
resource_name as ResName,
resource_approved as ResApproved,
resource_date as ResDate,

FROM
tbl_resources

WHERE
resource_date = @dEarliestDate

This will give me all records with the Min date value of the resource_date column. After this query, I need to find the Min resource_id value to return only one record.

----------
david

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 12:57:40

SELECT TOP 1 resource_id...
FROM resources
WHERE...

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-14 : 13:37:47

select top 1 myID, myDate

from t

order by myDate, myID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources