Query help needed ... - DML (SELECT, INSERT, UPDATE, DELETE) Questions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Query help needed ...

Query help needed ...

Options

Previous Topic · Next Topic

frank.svs

Posted: Tuesday, November 20, 2007 3:06:03 PM

Rank: Newbie

Joined: 11/20/2007
Posts: 1
Points: 3
Location: india

Hi pals,

I need small help.
I have a table with data as follows.

select * from test_data

c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103

Now i need to generate a new column c4 as sequence number in the output

I need to get the output something as follows

c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103

The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).

Any help would be greatly appreciated.

Thanks in advance,
franky

Scott Whigham

Posted: Wednesday, November 21, 2007 4:52:39 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

SELECT *, RANK() OVER(PARTITION BY c3 ORDER BY c1) FROM test should do it.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.