CODEWITHSUNDEEP
sql
cameraguy258
cameraguy258

Reputation: 689

SQL SELECT Sort by specific row

Suppose I have a table food, which I query SELECT name FROM food ORDER BY name:

| name
|--------
| Apple
| Banana
| Carrot
| Donut
...

I wish to specify that specific items of my choice be pinned to the top (e.g. "Lemon" and then "Carrot"), if they are in the table. Like so:

| name
| -------
| Lemon
| Carrot
| Apple
| Banana
| Donut
...

What kind of SQL query can I use to get this specific sort?

Upvotes: 3

Views: 3086

Answers (4)

Emil
Emil

Reputation: 336

I'd use CTE and go like this: 

WITH FoodOrder(name, foodOrder)
AS
(
    SELECT name
            , foodOrder = CASE name
                            WHEN 'Lemon' THEN 200
                            WHEN 'Carrot' THEN 100
                            ELSE 0
                        END                      
    FROM food   
)
SELECT name
FROM FoodOrder
ORDER BY foodOrder DESC, name

Please note that if you will choose enough spacing between CASE values (0, 100 ,200) you can easily add another prioritized food afterwards if needed without overwriting already existing values.

Upvotes: 0

Rich Andrews
Rich Andrews

Reputation: 1680

Chances are, this is going to be RDBMS specific. The one being used is not specified.

MySQL, commonly used, provides a function field(). Good for custom sorts on bounded values.

mysql> create temporary table food as select 'Apple' as food union select 'Banana' union select 'Carrot'  union select 'Donut';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from food;
+--------+
| food   |
+--------+
| Apple  |
| Banana |
| Carrot |
| Donut  |
+--------+
4 rows in set (0.00 sec)

mysql> select * from food order by field (food, 'Carrot', 'Apple', 'Banana', 'Donut');
+--------+
| food   |
+--------+
| Carrot |
| Apple  |
| Banana |
| Donut  |
+--------+
4 rows in set (0.00 sec)

Upvotes: 0

Hogan
Hogan

Reputation: 70538

Make a look up table like this:

WITH odering(F, Ord) AS
(
   VALUES ('Lemmon', 2),
          ('Carrot', 1)
)
SELECT name
FROM table t
LEFT JOIN ordering Ord on T.name = Ord.name
ORDER BY COALESCE(Ord.Ord, 0) DESC, Name ASC

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20609

You can use a case statement in your order by clause to prioritize items with a certain name.

The following will put Lemon and Carrot in priority order by assigning them the values of 1 and 2 from the case, where all others will get the value 3. Those remaining that were assigned 3 will then be sorted by the second expression in the order by clause, which is just the name column.

SELECT *
FROM food
ORDER BY
  CASE name
    WHEN 'Lemon' THEN 1
    WHEN 'Carrot' THEN 2
    ELSE 3
  END,
  name

Upvotes: 10

Related Questions