# Query help

I have a table that has a sequence number assigned in the form of 12-12345-1. As the sequence increases, the last digit increments by 1, so the next sequence number would be 12-12345-2. What I need to do is query the table to find all sequence numbers ending with '1' that do not have a '2' or greater. The sequence number is a varchar field, if that matters.

Anyone have an idea?

``````SELECT *
FROM YourTable T1
LEFT JOIN YourTable T2
ON LEFT(T1.seq_no, 8) = LEFT(T2.seq_no, 8)
AND T1.seq_no < T2.seq_no
WHERE RIGHT(T1.seq_no, 2) = '-1'
AND T2.seq_no IS NULL;``````
``````SELECT *
FROM YourTable T1
WHERE RIGHT(T1.seq_no, 2) = '-1'
AND NOT EXISTS
(
SELECT 1
FROM YourTable T2
WHERE LEFT(T2.seq_no, 8) = LEFT(T1.seq_no, 8)
AND T2.seq_no > T1.seq_no
);``````

etc

Thanks
This would work if all sequence numbers were the same length. I should have mentioned this...apologies The sequence numbers can have between 3-5 digits in that middle segment, so your suggestions would not work in all cases. The first segment will always be 2 digits, then a '-' then the middle segment, between 3-5 digits, than another '-' then the last segment could be between 1-9999.

``````; with
sample_data as
(
select    seq = '12-12345-1'    union all
select    seq = '12-12345-9999'    union all
select    seq = '12-99999-9999'
),
split_into_num as
(
select    *,
seq1 = convert(int, parsename(replace(seq, '-', '.'), 3)),
seq2 = convert(int, parsename(replace(seq, '-', '.'), 2)),
seq3 = convert(int, parsename(replace(seq, '-', '.'), 1))
from    sample_data
),
increment_it as
(
select    *,
ns1    = seq1 + (seq2 + (seq3 + 1) / 9999) / 99999,
ns2    = (seq2 + (seq3 + 1) / 9999) % 99999,
ns3    = (seq3 + 1) % 9999
from    split_into_num
)
select    *,
new_seq    = convert(varchar(5), ns1)
+ '-'
+ convert(varchar(5), ns2)
+ '-'
+ convert(varchar(5), ns3)
from    increment_it

/* result
seq           seq1        seq2        seq3        ns1         ns2         ns3         new_seq
------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------------
12-12345-1    12          12345       1           12          12345       2           12-12345-2
12-12345-9999 12          12345       9999        12          12346       1           12-12346-1
12-99999-9999 12          99999       9999        13          1           1           13-1-1
*/``````