SQL SELECT Sort by specific row

sql order by
ordering by the order of values in a sql in() clause
sql-order by number
sql order by specific sequence
sql-order by multiple columns
secondary sort in sql
select order
how order by works in sql

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?

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

Specific row at the top then sort the rest result set in SQL Server , In this post "Specific row at the top then sort the rest result set in SQL Server", we will learn how to customize the order by clause to keep a row� Specific row at the top then sort the rest result set in SQL Server 3 May 31, 2017 in SQL Server tagged set based example by Gopal Krishna Ranjan In this post, “ Specific row at the top then sort the rest result set in SQL Server “, we are going to learn that how we can order a result set in a customized way which cannot be achieved using

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

Sorting Rows with ORDER BY, You can use the ORDER BY clause to sort rows by a specified column or columns in ascending (lowest to highest) or descending (highest to lowest) order; see the “Sort Order” sidebar in this section. The ORDER BY clause always is the last clause in a SELECT statement. The SQL ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example, we show you how to Select First Row from each SQL Group. The following SQL Query will. First, partition the data by Occupation and assign the rank number using the yearly income. Next, ROW_NUMBER is going to select the First

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)

SQL: SELECT with ORDER BY | Computer programming, You can change the order of the rows by adding an ORDER BY clause at the end of your query, with a column name after. By default, the ordering will be in "� To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row#. You must move the ORDER BY clause up to the OVER clause. SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5; Here is the result set.

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.

Using OFFSET and FETCH with the ORDER BY , When results are sorted using the ORDER BY clause, then some options come into play to limit the number of rows returned: You can use TOP to return a specified� The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default. Syntax. The basic syntax of the ORDER BY clause which would be used to sort the result in an ascending or descending order is as follows −

SQL ORDER BY Keyword, ORDER BY DESC Example. The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by the "Country" column:� SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

SQL Server ORDER BY clause By Practical Examples, This tutorial shows you how to use the SQL Server ORDER BY clause to sort the result set of a query by a specified column list. You can order by a selected column or other expressions. Here an example, how to order by the result of a case-statement: SELECT col1 , col2 FROM tbl_Bill WHERE col1 = 0 ORDER BY -- order by case-statement CASE WHEN tbl_Bill.IsGen = 0 THEN 0 WHEN tbl_Bill.IsGen = 1 THEN 1 ELSE 2 END

ORDER BY Clause (Transact-SQL), Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are� The most commonly used function in SQL Server is the SQL ROW_NUMBER function. The SQL ROW_NUMBER function is available from SQL Server 2005 and later versions. ROW_NUMBER adds a unique incrementing number to the results grid. The order, in which the row numbers are applied, is determined by the ORDER BY expression.

Comments
  • He already added name to the order by clause, so it's good.
  • this was also the only answer which was not RDBMS specific @cameraguy258