MySQL select, between, AND, OR clause and query problem

Question

I have a table named item with two attributes (code and name).
Now i want to group them in the following way:

group a: code between (5300 and 5310),(7100,7200),(8210,8290)
group b: code not between (5300 and 5310),(7100,7200),(8210,8290)

How can i do it using MySQL query?

Answer 1

What do you mean by 'group'?

If you want to use GROUP BY you can do that:

SELECT
    CASE
        WHEN
            code BETWEEN 5300 AND 5310 OR
            code BETWEEN 7100 AND 7200 OR
            code BETWEEN 8210 AND 8290
        THEN 'Group A'
        ELSE 'Group B'
    END AS grp,
    COUNT(*) AS cnt
FROM your_table
GROUP BY grp

Perhaps you just want to ORDER BY though?

SELECT
    CASE
        WHEN
            code BETWEEN 5300 AND 5310 OR
            code BETWEEN 7100 AND 7200 OR
            code BETWEEN 8210 AND 8290
        THEN 'Group A'
        ELSE 'Group B'
    END AS grp,
    *
FROM your_table
ORDER BY grp

Also note that BETWEEN includes both endpoints which might not be what you meant.

Answer 2

Group a:

SELECT * FROM theTable 
WHERE 
  code >= 5300 AND code <= 5310 OR 
  code >= 7100 AND code <= 7200 OR  
  code >= 8210 AND code <= 8290

Group b:

SELECT * FROM theTable 
WHERE 
  code < 5300 OR
  code > 5310 AND code < 7100 OR
  code > 7200 AND code < 8210 OR
  code > 8290

Everything

SELECT *, 'A' FROM theTable 
WHERE 
  code >= 5300 AND code <= 5310 OR 
  code >= 7100 AND code <= 7200 OR  
  code >= 8210 AND code <= 8290
UNION
SELECT *, 'B' FROM theTable 
WHERE 
  code < 5300 OR
  code > 5310 AND code < 7100 OR
  code > 7200 AND code < 8210 OR
  code > 8290