Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL Server: For every 3 unique ids group together and set groupid without using CASE Expression

Table table_name

id
1
2
3
4
4
5
6
6
7
7
7
8
9
9
10

Following SQL will give me the answer Demo but I need to hardcode each case and I need to know the MAX(id) in the first place.

SELECT id,
CASE 
WHEN id > '0' AND id <= '3' THEN 1 
WHEN id > '3' AND id <= '6' THEN 2 
WHEN id > '6' AND id <= '9' THEN 3 
WHEN id > '9' AND id <= '12' THEN 4
END AS groupid
FROM table_name

How to improve it without me hardcode using the CASE. Since id column will continue increasing and it will runout of case sometime later.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

(SQL Server 2014 – build v12.0.6108.1)

Result:

id groupid
1 1
2 1
3 1
4 2
4 2
5 2
6 2
6 2
7 3
7 3
7 3
8 3
9 3
9 3
10 4

>Solution :

SELECT id, CEILING(id/3.0) AS groupid
FROM table_name

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading