Simple query (I Hope) - 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 2005 Forums

Express Edition and Compact Edition (2005)

Simple query (I Hope)

Author

Topic

delfeye
Starting Member

1 Post

Posted - 2014-02-14 : 13:48:54

I don't work in SQL typically (ever), but I need to modify a query to only return the first instance of EventTime for IdNumber.

SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM [Audit]
WHERE EventTime >='[today]'
AND [Event] = 'Valid access';

The query was already built in to the physical Access Control application we support, but my customer wants to know the first time an employee badges in. I looked around on the site, but if I found the right solution, it was above my total lack of SQL skills. The application uses SQL 2005 Express.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2014-02-18 : 04:04:02

SELECT TOP 1 [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM [Audit]
WHERE EventTime >='2014-02-18'
AND [Event] = 'Valid access'
ORDER BY EventTime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-18 : 13:36:43

return the first instance of EventTime for IdNumber.
assuming you want it for a group of id this


SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber]
FROM
(
SELECT [Id], [EventTime], [Location], [Event], [Details], [Dept], [IdNumber],
ROW_NUMBER() OVER (PARTITION BY [IdNumber] ORDER BY [EventTime]) AS Seq
FROM [Audit]
WHERE EventTime >='[today]'
AND [Event] = 'Valid access'
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources