Grouping records by some columns

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 2008 Forums

Transact-SQL (2008)

Grouping records by some columns

Author

Topic

CostaSBD
Starting Member

5 Posts

Posted - 2011-08-18 : 08:17:32

Hello!

I've a table in the databases of an access control system that store the accesses of the people, and have some columns as the UserID, TransactionTime and others...

I would like to obtain the first record of each user for each day of a selected period (start date - end date)

Example:

If these are the rows for user 00001

01/01/2011 08:00:00 00001
01/01/2011 10:30:00 00001
01/01/2011 12:15:00 00001

02/01/2011 08:00:00 00001
02/01/2011 10:00:00 00001

04/01/2011 08:00:00 00001

07/01/2011 08:00:00 00001
07/01/2011 12:00:00 00001

I am looking for the following:

01/01/2011 08:00:00 00001
02/01/2011 08:00:00 00001
04/01/2011 08:00:00 00001
07/01/2011 08:00:00 00001

I've Attached a script to create the table and records. I'm working with SQL Server 2008.

http://dl.dropbox.com/u/7722333/tableRegistro.sql[url][/url]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-18 : 08:20:20

[code]
select *
from
(
select *, row_no = row_number() over ( partition by dateadd(day, datediff(day, 0, datecol), 0) order by datecol)
from yourtable
) t
where t.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 09:32:27

shouldnt it be?


select *
from
(
    select *, row_no = row_number() over ( partition by dateadd(day, datediff(day, 0, datecol), 0),usercol order by datecol)
    from   yourtable
) t
where t.row_no = 1

as we want it for each user

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources