Display top 1 by datetime - SQL Server Forums

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)

Display top 1 by datetime

Author

Topic

dgarcia59
Starting Member

7 Posts

Posted - 2007-03-22 : 13:16:28

I have a view that shows the record from the Alert table with the lowest AlertDate (DateTime) value per ParentId (uniqueidentifier) as you can see below:

create view "AlertTop" as
SELECT Id, AlertDate, Text, ParentId
FROM Alert
WHERE (AlertDate =
(SELECT MIN(AlertDate)
FROM Alert AS a
WHERE a.ParentId = Alert.ParentId
GROUP BY a.ParentId))

My problem is that if there are two rows with the exact same AlertDate and ParentId there will be two rows for a particular ParentId in the view. I want ParentId to be unique in the view. I don't really care which Alert is returned as long as only one is returned for each ParentId.

How do I change the view's SQL to perform this? Do I need to make another view or a temporary table as a helper? I am more of a programmer than a database administrator so I don't have much SQL experience. So any help or tips is greatly appreciated ^_^

Thanks!

dgarcia59
Starting Member

7 Posts

Posted - 2007-03-22 : 13:21:48

To expand slightly on what I'm trying to do... This view is used on a JOIN in a stored procedure. Because ParentId isn't unique in the view I can end up getting multiple rows returned by the join since I'm using an INNER JOIN to connect the Alert table to the Parent table. Maybe some other type of join used in the stored proc would fix this problem?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 19:28:43

what is the primary key of the Alert table ?

dgarcia59
Starting Member

7 Posts

Posted - 2007-03-22 : 19:43:25

Id is the primary key, it is of type uniqueidentifier.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-22 : 21:30:04

"shows the record from the Alert table with the lowest AlertDate (DateTime) value per ParentId (uniqueidentifier)"


SELECT Id, AlertDate, Text, ParentId
FROM   Alert a
WHERE  Id = (SELECT TOP 1 Id
             FROM  Alert AS x
             WHERE x.ParentId = a.ParentId
             ORDER BY BY x.AlertDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources