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%'

Hi there I'm using SQL server

DJR
– DJR

2017-01-24 06:19:48 +00:00
Commented Jan 24, 2017 at 6:19 — DJR
– DJR, Commented Jan 24, 2017 at 6:19

Hogan · Accepted Answer · 2017-01-23 16:17:52Z

1

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.

edited Jan 23, 2017 at 16:17

answered Jan 23, 2017 at 16:12

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David דודו Markovitz Over a year ago

The OP gave an example, not an hard-coded boundaries.

Hogan Over a year ago

I've no idea what you mean @DuduMarkovitz -- I believe we read the same question. If he wants a group by he can use ORD.ORD in the group by statement. This join will be much faster than using the substr function

David דודו Markovitz · Accepted Answer · 2017-01-23 18:58:18Z

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 |
+----------+-----+-----------+---------+---------+

Collectives™ on Stack Overflow

Sequential SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related