How to Return Rows with a Specific Value First

How do I return rows with a specific value first?

On SQL Server, Oracle, DB2, and many other database systems, this is what you can use:

ORDER BY CASE WHEN city = 'New York' THEN 1 ELSE 2 END, city

How do I return rows with a specific value first and order by a different column?

Try this:

SELECT *
  FROM answer
  WHERE parentid = '{$question_id}'
  ORDER BY CASE id WHEN '{$answerid}' THEN 1 ELSE 2 END ASC, score DESC

How do i return rows with specific value if else?

Below query will help you

SELECT * FROM Position ORDER BY 
CASE  WHEN position_value = 1 THEN created_at END ASC,
CASE WHEN position_value = 2 THEN created_at END DESC

TSQL - How to return rows with a specific value last?

If you wish to maintain the union then you could do something like this:

select * from (
    select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription, 1 as order
    from codes
    where code_id > '000003' and code_id < '000100' 

    UNION

    select 'T' as Success,  code_id as CategoryID, code_desc as CategoryDescription, 2 as order
    from codes
    where code_id > '000001' and code_id < '000004'
) x
order by x.order

Return a column value only once in row 1 and return NULL for other rows

You can use row_number() :

select t.col2, (case when t.seq = 1 then t.col2 end) as col2, t.col3
from (select t.col1, t.col2, t.col3,
             row_number() over (partition by t.col2 order by ?) as seq
      from t
      where . . . 
     ) t;

SQL Order By: Specific Value First, then Ordering?

Use a case expression to put Needs Response rows first. Then order by date descending:

order by case when status = 'Needs Response' then 0 else 1 end, date desc

How to select only the first rows for each unique value of a column?

A very simple answer if you say you don't care which address is used.

SELECT
    CName, MIN(AddressLine)
FROM
    MyTable
GROUP BY
    CName

If you want the first according to, say, an "inserted" column then it's a different query

SELECT
    M.CName, M.AddressLine,
FROM
    (
    SELECT
        CName, MIN(Inserted) AS First
    FROM
        MyTable
    GROUP BY
        CName
    ) foo
    JOIN
    MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

while return rows with a specific value first getting syntax error

Try this

select IDName 
from Type_ofID_tbl 
where deleted=0 order by case when IDName ='Emirates ID' then 1 else 2 end

MySQL - Select query with first rows where a field value 10 without ordering by this field

Try using ORDER BY along with a CASE expression:

SELECT *
FROM player
ORDER BY
    CASE WHEN total_games > 10 THEN 0 ELSE 1 END,
    signin_year,
    player_type;

This places players having played more than 10 total games first, followed by all other players. Within these two blocks, the data is sorted ascending by signin year followed by ascending player type.

---

Related Topics