Sequential SQL query

Question

A newby to SQL question so please forgive me

Any clues of a simple SQL query? I am trying to sort a series a results where there is a break in a range of numbers.

eg R00000D to R99999D.

However some of the number have been allocated in a none sequential order example R00000D to R12549D then the next sequence is R200001D to R50000D.

So I would like to group these in blocks

i.e

R00000D to R12549D
R20000D to R50000D
R69154D to R99999D

The only way that I have been able to do is to export to excel and a long winded way and counting the columns.

My start query is a simple

Select *
FROM SystemNote
WHERE Note LIKE 'R%'

Answer 1

For use with databases that support the windows functions. Might require small changes for different providers.

Demo

create table SystemNote (Note char(7));

insert into SystemNote values 
 ('R00000D'),('R00001D'),('R00002D'),('R00003D'),('R00004D')
,('R00012D'),('R00013D')
,('R00015D'),('R00016D'),('R00017D'),('R00018D')
,('R00021D')
,('R00025D'),('R00026D'),('R00027D')

---

select      min (Note)  as from_Note
           ,max (Note)  as to_Note
           ,count (*)   as Notes

from       (select      Note
                       ,row_number () over (order by Note)  as rn

            from        SystemNote

            where       Note like 'R%'
            ) sn

group by    cast (substr(Note,2,5) as int) - rn

order by    from_Note

---

+-----------+---------+-------+
| from_note | to_note | notes |
+-----------+---------+-------+
| R00000D   | R00004D | 5     |
+-----------+---------+-------+
| R00012D   | R00013D | 2     |
+-----------+---------+-------+
| R00015D   | R00018D | 4     |
+-----------+---------+-------+
| R00021D   | R00021D | 1     |
+-----------+---------+-------+
| R00025D   | R00027D | 3     |
+-----------+---------+-------+

---

select  dense_rank () over (order by cast (substr(Note,2,5) as int)-rn) as group_id
       ,row_number () over (partition by cast (substr(Note,2,5) as int)-rn order by note) as seq
       ,min (Note) over (partition by cast (substr(Note,2,5) as int)-rn) as from_Note
       ,max (Note) over (partition by cast (substr(Note,2,5) as int)-rn) as to_Note
           ,Note

from       (select      Note
                       ,row_number () over (order by Note)  as rn

            from        SystemNote

            where       Note like 'R%'
            ) sn

order by    Note

---

+----------+-----+-----------+---------+---------+
| group_id | seq | from_note | to_note | note    |
+----------+-----+-----------+---------+---------+
| 1        | 1   | R00000D   | R00004D | R00000D |
+----------+-----+-----------+---------+---------+
| 1        | 2   | R00000D   | R00004D | R00001D |
+----------+-----+-----------+---------+---------+
| 1        | 3   | R00000D   | R00004D | R00002D |
+----------+-----+-----------+---------+---------+
| 1        | 4   | R00000D   | R00004D | R00003D |
+----------+-----+-----------+---------+---------+
| 1        | 5   | R00000D   | R00004D | R00004D |
+----------+-----+-----------+---------+---------+
| 2        | 1   | R00012D   | R00013D | R00012D |
+----------+-----+-----------+---------+---------+
| 2        | 2   | R00012D   | R00013D | R00013D |
+----------+-----+-----------+---------+---------+
| 3        | 1   | R00015D   | R00018D | R00015D |
+----------+-----+-----------+---------+---------+
| 3        | 2   | R00015D   | R00018D | R00016D |
+----------+-----+-----------+---------+---------+
| 3        | 3   | R00015D   | R00018D | R00017D |
+----------+-----+-----------+---------+---------+
| 3        | 4   | R00015D   | R00018D | R00018D |
+----------+-----+-----------+---------+---------+
| 4        | 1   | R00021D   | R00021D | R00021D |
+----------+-----+-----------+---------+---------+
| 5        | 1   | R00025D   | R00027D | R00025D |
+----------+-----+-----------+---------+---------+
| 5        | 2   | R00025D   | R00027D | R00026D |
+----------+-----+-----------+---------+---------+
| 5        | 3   | R00025D   | R00027D | R00027D |
+----------+-----+-----------+---------+---------+

Answer 2

I would make a table with your ordering requirements and join to it -- this allows you to modify the table if your requirements change. Here I do it as an inline table but you could have an actual table or CTE.

This code should work on most platforms with slight modifications. I know it will work on DB2 and I believe Oracle. Not sure if the values statement works in SQL Server. You might need to change to SELECT / UNION ALLs.

SELECT *
FROM SystemNote
LEFT JOIN (
  VALUES
    ('R00000D','R12549D',1),
    ('R20000D','R50000D',2),
    ('R69154D','R99999D',3)
) AS ORD(S,E,ORD) ON Note BETWEEN ORD.S AND ORD.E
WHERE Note LIKE 'R%'
ORDER BY COALESCE(ORD.ORD,4) ASC, Note

Note, this puts any item not in your defined ranges at the end. This may (or may not) be what you want. You didn't define it.