Need help on query - 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

Transact-SQL (2005)

Need help on query

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-18 : 05:45:14

I've table and row as follow,
trip
DDate | TID | RID | TimerID
------------------------------------------------------------------------
2010-02-18 00:00:00.000 _d091100007569 _r00000006 _t00000033
2010-02-18 00:00:00.000 _d091100007571 _r00000006 _t00000035
2010-02-18 00:00:00.000 _d091100007574 _r00000006 _t00000038
2010-02-18 00:00:00.000 _d091100007581 _r00000007 _t00000042
2010-02-18 00:00:00.000 _d091100007582 _r00000007 _t00000043
2010-02-18 00:00:00.000 _d091100007635 _r00000026 _t00000111
2010-02-19 00:00:00.000 _d091100007711 _r00000007 _t00000042
2010-02-19 00:00:00.000 _d091100007712 _r00000007 _t00000043
2010-02-20 00:00:00.000 _d091100007842 _r00000007 _t00000043
2010-02-20 00:00:00.000 _d091100007852 _r00000010 _t00000056
2010-02-20 00:00:00.000 _d091100007895 _r00000026 _t00000111
2010-02-21 00:00:00.000 _d091100007962 _r00000006 _t00000036
2010-02-21 00:00:00.000 _d091100007964 _r00000006 _t00000038
2010-02-21 00:00:00.000 _d091100007971 _r00000007 _t00000042
2010-02-21 00:00:00.000 _d091100007972 _r00000007 _t00000043
/*TID is a unique*/

In 18 Feb 2010 have 3 RID such as _r00000006,_r00000007,_r00000026
In 19 Feb 2010 have 1 RID such as _r00000007
In 20 Feb 2010 have 3 RID such as _r00000007,_r00000010,_r00000026
In 21 Feb 2010 have 2 RID such as _r00000006,_r00000007

Based on TimerID, how to query and the resultset as follow,
DDate | TID | RID | TimerID | Seq
----------------------------------------------------------------------------------
2010-02-18 00:00:00.000 _d091100007569 _r00000006 _t00000033 1
2010-02-18 00:00:00.000 _d091100007571 _r00000006 _t00000035 2
2010-02-18 00:00:00.000 _d091100007574 _r00000006 _t00000038 3
2010-02-18 00:00:00.000 _d091100007581 _r00000007 _t00000042 1
2010-02-18 00:00:00.000 _d091100007582 _r00000007 _t00000043 2
2010-02-18 00:00:00.000 _d091100007635 _r00000026 _t00000111 1
2010-02-19 00:00:00.000 _d091100007711 _r00000007 _t00000042 1
2010-02-19 00:00:00.000 _d091100007712 _r00000007 _t00000043 2
2010-02-20 00:00:00.000 _d091100007842 _r00000007 _t00000043 1
2010-02-20 00:00:00.000 _d091100007852 _r00000010 _t00000056 1
2010-02-20 00:00:00.000 _d091100007895 _r00000026 _t00000111 1
2010-02-21 00:00:00.000 _d091100007962 _r00000006 _t00000036 1
2010-02-21 00:00:00.000 _d091100007964 _r00000006 _t00000038 2
2010-02-21 00:00:00.000 _d091100007971 _r00000007 _t00000042 1
2010-02-21 00:00:00.000 _d091100007972 _r00000007 _t00000043 2

Need help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 05:51:03

[code]SELECT *
FROM
(SELECT ROW_NUMBER OVER (PARTITION BY DDate,TimerID ORDER BY RID) AS Seq,*
FROM Table
)t
[/code]

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 05:58:43

PARTITION BY DDate,RID ORDER BY DDate,RID,TimerID

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 06:00:56

quote:
Originally posted by webfred

PARTITION BY DDate,RID ORDER BY DDate,RID,TimerID

No, you're never too old to Yak'n'Roll if you're too young to die.

Will that work?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 06:03:22

I think your "PARTITION BY DDate,TimerID "
will start over with 1 for each row.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 06:45:16

quote:
Originally posted by webfred

I think your "PARTITION BY DDate,TimerID "
will start over with 1 for each row.

No, you're never too old to Yak'n'Roll if you're too young to die.

You were right Webfred

I got confused by the formatting of sample output. Misread column names. Apologies

however I think you need only this

PARTITION BY DDate,RID ORDER BY TimerID

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-18 : 07:15:16

Yes, but I like it to give the fitting order by as well

No, you're never too old to Yak'n'Roll if you're too young to die.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-19 : 02:12:12

quote:
Originally posted by visakh16

SELECT *
FROM
(SELECT ROW_NUMBER OVER (PARTITION BY DDate,TimerID ORDER BY RID) AS Seq,*
FROM Table
)t

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

So far, i've a s follow,
declare @trip table
(DDate datetime,TID varchar(20),RID varchar(10),TimerID varchar(10))
insert into @trip values('2010-02-18 00:00:00.000','_d091100007569','_r00000006','_t00000033')
insert into @trip values('2010-02-18 00:00:00.000', '_d091100007571', '_r00000006', '_t00000035')
insert into @trip values('2010-02-18 00:00:00.000', '_d091100007574', '_r00000006', '_t00000038')
insert into @trip values('2010-02-18 00:00:00.000', '_d091100007581', '_r00000007' ,'_t00000042')
insert into @trip values('2010-02-18 00:00:00.000', '_d091100007582' ,'_r00000007', '_t00000043')
insert into @trip values('2010-02-18 00:00:00.000', '_d091100007635', '_r00000026' ,'_t00000111')
insert into @trip values('2010-02-19 00:00:00.000', '_d091100007711', '_r00000007' ,'_t00000042')
insert into @trip values('2010-02-19 00:00:00.000', '_d091100007712' ,'_r00000007', '_t00000043')
insert into @trip values('2010-02-20 00:00:00.000', '_d091100007842' ,'_r00000007', '_t00000043')
insert into @trip values('2010-02-20 00:00:00.000' ,'_d091100007852' ,'_r00000010' ,'_t00000056')
insert into @trip values('2010-02-20 00:00:00.000' ,'_d091100007895' ,'_r00000026', '_t00000111')
insert into @trip values('2010-02-21 00:00:00.000', '_d091100007962', '_r00000006', '_t00000036')
insert into @trip values('2010-02-21 00:00:00.000' ,'_d091100007964', '_r00000006' ,'_t00000038')
insert into @trip values('2010-02-21 00:00:00.000', '_d091100007971', '_r00000007', '_t00000042')
insert into @trip values('2010-02-21 00:00:00.000', '_d091100007972' ,'_r00000007', '_t00000043')

SELECT *
FROM
(SELECT ROW_NUMBER OVER (PARTITION BY DDate,RID ORDER BY DDate,RID,TimerID) AS Seq,*
FROM @trip
)t

Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'OVER'.
What happen?

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-02-19 : 02:15:02

oh, row_number()

sorry guys, both of you really great

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-19 : 07:08:50