CODEWITHSUNDEEP
mysqlsql
Kim
Kim

Reputation: 5425

SQL - How to add this custom order for my select query?

i'm using mysql, and i want to add some custom order for my select query.

For example i'm having this table name A, there are column 'a' and 'b'. And I can assure that 'b' is bigger than 'a'.

|a|b|
|3|4|
|1|9|
|2|7|
|6|9|
|8|9|
|2|6|
|4|8|

I want to select them out and order by value c = 5, the order rule is:

if c is less than both a and b then this is weight 1.

if c is between a and b then this is weight 2.

if c is bigger than both a and b then this is weight 3.

and then order by this weight value.

(the order of the same weight does not need to be considered here.)

so the result should be:

|a|b|
|6|9| -> weight 1
|8|9| -> weight 1
|1|9| -> weight 2
|2|6| -> weight 2
|2|7| -> weight 2
|4|8| -> weight 2
|8|9| -> weight 3

So how do I write this select query?

PS: It doesn't have to specify weight 1, 2 and 3 in the query, the weight I 'invented' above myself is just to address the order rule!

Upvotes: 0

Views: 89

Answers (4)

Debmalya Banerjee
Debmalya Banerjee

Reputation: 45

SELECT a, b FROM A    
ORDER BY
CASE WHEN a>5 AND b>5 THEN 1
 WHEN a < 5 AND 5 < b THEN 2
 WHEN 5 > a AND 5 > b THEN 3
END;

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

You can use CASE for this:

ORDER BY CASE WHEN @c < a AND @c < b THEN 1
              WHEN a < @c AND @c < b THEN 2
              WHEN @c > a AND @c > b THEN 3
         END

Upvotes: 3

Pratik Patel
Pratik Patel

Reputation: 78


DECLARE @c INT = 5
DECLARE @tab TABLE(a INT, b INT)
INSERT INTO @tab
SELECT 3,4 UNION ALL 
SELECT 1,9 UNION ALL 
SELECT 2,7 UNION ALL
SELECT 6,9 UNION ALL
SELECT 8,9 UNION ALL
SELECT 2,6 UNION ALL
SELECT 4,8

SELECT *,
CASE WHEN @c < a AND @c < b THEN 1 
     WHEN @c BETWEEN a AND B THEN 2
     WHEN @c > a AND @c > b THEN 3
END 
FROM @tab
ORDER BY CASE WHEN @c<a AND @c<b THEN 1 
     WHEN @c BETWEEN a AND B THEN 2
     WHEN @c>a AND @c>b THEN 3
END

Upvotes: 0

Haytem BrB
Haytem BrB

Reputation: 1499

Select a, b from (
Select a, b, 
case when a>5 and b>5 then 1 when a<5 and b>5 then 2 when a<5 and b>5 then 3 end as weight
order by weight)w

Upvotes: 0

Related Questions