query help.. pls

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

SELECT sno,

id,

product,

ROW_NUMBER() OVER(PARTITION BY id ORDER BY sno)

FROM yourTable

ORDER BY sno

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

With 166 visits you should know how to post code so that we can readily give you a tested answer. So next time please do so.

CREATE TABLE #T(sno INT,ID INT,product VARCHAR(10))

INSERT INTO #T

SELECT 1, 1000, 'aaaaa' UNION ALL

SELECT 2, 1001, 'bbbbb' UNION ALL

SELECT 3, 1000, 'tttttt' UNION ALL

SELECT 5, 1000, 'ddddd' UNION ALL

SELECT 6, 1001, 'hhhhh' UNION ALL

SELECT 7, 1002, 'vvvvv'

Here some T-SQL that will produce what you asked for

;with numbered as(SELECT rowno=row_number() over

(partition by id order by SNO),sno,ID,product from #T)

select * from numbered ORDER BY SNO

Results:

rownosnoIDproduct

111000aaaaa

121001bbbbb

231000tttttt

351000ddddd

261001hhhhh

171002vvvvv

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]