CODEWITHSUNDEEP
mysqlsql
skycrew
skycrew

Reputation: 918

MySQL Get Range of Serial Numbers

I have difficulty to retrieve range of serial numbers in my database due to improper database design.

I've got data as below :-

  1. XY100001 - XY101000 | User_A ---> 1000 data
  2. XY101001 - XY102000 | User_B ---> 1000 data
  3. XY102001 - XY102500 | User_C ---> 500 data
  4. XY102501 - XY103000 | User_A ---> 1000 data

and the data will be store in database as below :-

| 1    | User_A | XY100001 | DateTime |
| 2    | User_A | XY100002 | DateTime |
..
| 1000 | User_A | XY101000 | DateTime |
| 1001 | User_B | XY101001 | DateTime |
| 1002 | User_B | XY101002 | DateTime |
..
| 2000 | User_B | XY102000 | DateTime |
| 2001 | User_C | XY102001 | DateTime |
| 2002 | User_C | XY102002 | DateTime |
..
| 2500 | User_C | XY102500 | DateTime |
| 2501 | User_A | XY102501 | DateTime |
| 2502 | User_A | XY102502 | DateTime |
..
| 3000 | User_A | XY103000 | DateTime |

I tried to select min and max serial number, after that group by DateTime but still cant get correct ranges because each Users will have multiple ranges.

The sample output if I'm using min and max as below :-

| User_A | XY100001 | XY103000 |
| User_B | XY101001 | XY102000 |
| User_C | XY102001 | XY102500 |

You can see, wrong output for User_A. User_A should have 2 ranges (XY100001-XY101000) and (XY102501-XY103000)

Question is, how can I retrieve back all the ranges?

Any ideas ?

Upvotes: 0

Views: 323

Answers (1)

peterm
peterm

Reputation: 92805

A possible solution 

SELECT user, MIN(sn) range_start, MAX(sn) range_end
  FROM
(
  SELECT user, sn, @r := IF(@g = user, @r, @r + 1) range_num, @g := user
    FROM table1 CROSS JOIN (SELECT @r := 0, @g := NULL) i
   ORDER BY sn
) q
 GROUP BY user, range_num

Sample output:

|   USER | RANGE_START | RANGE_END |
|--------|-------------|-----------|
| User_A |    XY100001 |  XY101000 |
| User_A |    XY102501 |  XY103000 |
| User_B |    XY101001 |  XY102000 |
| User_C |    XY102001 |  XY102500 |

Here is SQLFiddle demo

Upvotes: 1

Related Questions