Query Assistance 1

[NWChowd]

Hi,

I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:


ClaimNumber LineNumber SequenceNumber
abc123 1 1
abc123 2 2
abc123 3 3
def321 5 1
def321 6 2
ghi456 2 1
jkl789 3 1
jkl789 4 2


So...
SELECT ClaimNumber, LineNumber, <Some Logic> AS SequenceNumber FROM MyTable


Is there any way to do this?


Thanks,
Dennis

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[vongrunt]

Very common question - so here we go again:

select A.ClaimNumber, A.LineNumber, count(*) as SequenceNumber
from blah A
inner join blah B on A.ClaimNumber = B.ClaimNumber and A.LineNumber >= B.LineNumber
group by A.ClaimNumber, A.LineNumber
order by A.ClaimNumber, A.LineNumber

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[maswien]

try this:

select t0.ClaimNumber, t0.LineNumber, 
       t0.LineNumber - t1.min_line_number + 1
from myTable t0 inner join 
( select ClaimNumber,  min(LineNumber) as min_line_number 
  from myTable group by ClaimNumber ) t1
  on t0.ClaimNumber = t1.ClaimNumber

 

[SQLDenis]

maswien I still owed you a star from Thanksgiving, I fulfilled my promise

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[maswien]

Thanks Denis, I like this star very much ;-)

 

[unclerico]

maswien, why do you do this??

select t0.ClaimNumber, t0.LineNumber, 
       t0.LineNumber - t1.min_line_number + 1
from myTable t0 inner join 
[b]( select ClaimNumber,  min(LineNumber) as min_line_number [/b]
  from myTable group by ClaimNumber ) t1
  on t0.ClaimNumber = t1.ClaimNumber

I am not questioning your code I am just trying to learn the different situations to use such code. thanks.

 

[maswien]

Unclerico, look the data sample posted, the LineNumber will be a integer sequence start with any value, so what we need is minus a value from lineNumber column and get the integer sequence start from 1.