CODEWITHSUNDEEP
mysql
ani0710
ani0710

Reputation: 266

Split a column into a defined range in MYSQL

I have a table which looks like this:

+-----------------------
| id     | first_name 
+-----------------------
| AC0089 | John      |
| AC0015 | Dan       |
| AC0017 | Marry     |
| AC0003 | Andy      |
| AC0001 | Trent     |
| AC0006 | Smith     |
+-----------------------

I need a query to split the id in the range of 3 and also display the starting id of that range i.e.

+------------+----------+--------
| startrange | endrange | id                  
+------------+----------+--------
|          1 |       3 | AC0089 
|          4 |       6 | AC0003 
+------------+----------+--------

I am pretty new to SQL and trying the below query but I dont think I am near to the correct solution at all ! Here is the query:

select startrange, endrange, id from table inner join (select 1 startRange, 3 endrange union all select 4 startRange, 6 endRange) r group by r.startRange, r.endRange;

It is giving the same id every-time and I am not able to come up with any other solution. How Can I get the required output?

Upvotes: 1

Views: 156

Answers (2)

Hardeep Pandya
Hardeep Pandya

Reputation: 917

Try this

SET @ct := 0;
select startrange,(startrange + 2) as endrange, seq_no from
(select (c.st - (select count(*) from <table_name>)) as startrange,  c.* from
(select (@ct := @ct + 1) as st, b.* from <table_name> as b) c
having startrange mod 3 = 1) as cc;

sorry for formating.

Upvotes: 1

jbay
jbay

Reputation: 126

I'm not completely sure what your trying to do but if you're trying to convert a table of ID's into ranges use a case when.

CASE WHEN startrange in(1,2,3) THEN 1
     ELSE NULL
END as startrange,

CASE WHEN endrange in(1,2,3) THEN 3
     ELSE NULL
END as endrange,

CASE WHEN ID in(1,2,3) THEN id 
     WHEN ID in(4,5,6) THEN id
     ELSE id
END AS ID

Upvotes: 0

Related Questions