logic in statement - 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)

logic in statement

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-22 : 08:48:22

Hi I have the following statement:

Select field1, field2, date1
from table1
order by date1

I just want to add by in order of date a number at the end of each row.

So for example:

field1, field2, date1, number
jon, paul, 06/05/09, 1
jon, paul, 07/05/09, 2

etc.

How would I add that logic?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-22 : 09:14:13

SELECT field1, field2, date1
,[Number] = RANK() OVER(Partition by field1,field2 order by date1 asc)

from yourtable

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-22 : 13:40:53

If you want the numbers to reset after each Field1 and Field2 grouping Jim's query will work. If you just whant a sequential numbering then you can get rid of the PARTITION clause:

SELECT 
	field1, 
	field2, 
	date1,
	RANK() OVER(ORDER BY date1 ASC) AS [Number]
FROM
	table1
ORDER BY 
	date1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources