Help Needed for SQL Query

Question

Help Needed for SQL Query

yatish.patil

Ten Centuries

Points: 1268
More actions
July 19, 2011 at 7:52 am

#99747

Hello Everyone,
I am trying to solve one sql query issue, I need help to solve this issue,
the test code is as below
Create TABLE #History
(
HistoryID INT,
Id INT,
letterId INT,
Letter VARCHAR(2),
HistoryRank INT
)
INSERT INTO #History VALUES (1101795,265805,5,'NW',11)
INSERT INTO #History VALUES (1119363,265805,5,'NW',10)
INSERT INTO #History VALUES (1182858,265805,4,'A+',9)
INSERT INTO #History VALUES (1237349,265805,3,'A',8)
INSERT INTO #History VALUES (1350251,265805,3,'A',7)
INSERT INTO #History VALUES (1442353,265805,3,'A',6)
INSERT INTO #History VALUES (1610381,265805,3,'A',5)
INSERT INTO #History VALUES (1931359,265805,2,'B',4)
INSERT INTO #History VALUES (2030510,265805,5,'NW',3)
INSERT INTO #History VALUES (2138415,265805,5,'NW',2)
INSERT INTO #History VALUES (2457793,265805,3,'A',1)
SELECT * FROM #History
DROP TABLE #History
what Output I am looking is as below,
HistoryID Id letterId Letter HistoryRank SequenceNumber
----------- ----------- ----------- ------ ----------- --------------
1101795 265805 5 NW 11 0
1119363 265805 5 NW 10 0
1182858 265805 4 A+ 9 1
1237349 265805 3 A 8 2
1350251 265805 3 A 7 2
1442353 265805 3 A 6 2
1610381 265805 3 A 5 2
1931359 265805 2 B 4 3
2030510 265805 5 NW 3 4
2138415 265805 5 NW 2 4
2457793 265805 3 A 1 5
what I am trying to do is that grouping by letterId but while doing so if there is any other letterid between two history records then it should start new Sequence Number for this.
Please suggest me any solution for the desired output
Thank you
Yatish

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Any solution that depends on row-sequence like that is going to end up being some form of cursor or another.

So, either use a real cursor (static should perform well on this), or use a "quirky update" solution. Since quirky updates are touchy, I'd use either a T-SQL cursor or a CLR one.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

srikant maurya Hall of Fame Points: 3293 More actions · Answer 2

Below query will work for yur case ,

SELECT * ,dense_rank() over ( order by letterId desc ) FROM #History

order by HistoryID

yatish.patil Ten Centuries Points: 1268 More actions · Answer 3

Hi srikant maurya,

Thank you for your reply, but in this case it will group all letters together and not as required for me...I mean if you look at output you will find that all the letters with 'NW' are grouped with 1 but they should be different first two rows should be 1 and the next set of two rows should be different as their are other letters between these set of two rows.

Please let me know if I am not clear in my explanation.

Thank you

Yatish

Ken McKelvey SSCoach Points: 18768 More actions · Answer 4

You asked a similar question before on this thread:

http://www.sqlservercentral.com/Forums/Topic1139620-145-1.aspx#bm1139686

I suggest you study the original thread so you understand what is happening.

yatish.patil Ten Centuries Points: 1268 More actions · Answer 5

Hello Everyone,

Thank you for your time, I am able to find the solution with reference to same query at http://www.sqlservercentral.com/Forums/Topic1139620-145-1.aspx

Thank you

Yatish