CODEWITHSUNDEEP
sqlmysql
user222585
user222585

Reputation: 171

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

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?

Upvotes: 1

Views: 1138

Answers (2)

Amarghosh
Amarghosh

Reputation: 59471

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839234

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.

Upvotes: 4

Related Questions