Order by Condition

ORDER BY condition

Your first example looks almost there to me.

SELECT * FROM foo ORDER BY id IN (1,2,3) DESC, created_date ASC

Added DESC because id IN (1,2,3) returns 1 if true or 0 if false. 1 > 0, so ordering them in descending order gets the desired result.

Added ASC because I like to be explicit.

Based on your later examples, I think what you're missing is that, though it contains spaces, field IN (list) is a single operator that returns 0 or 1. IF(id IN (1,2,3), 0, 1) is inherently redundant.

As such, you shouldn't need to use a UNION or sort manually, since MySQL makes this simpler than you even realize :)

SQL Query with conditional order by for a specific condition

When I face this kind of situation, I generally do this:

SELECT [CompanyName]
     , [CompanyCode]
     , SortOrder = case when CompanyName is null then 3
                        when CompanyName = 'MyCompany' then 1
                        else 2
                    end
  FROM [dbo].COND_ORDERBY_TEST
 ORDER BY 3 asc, CompanyName asc

As you are always ordering by the same field, SQL Server will not guess what order do you want...

Try it :)

Order by 2 conditions in SQL?

Order by pin first then order by id. Add the DESC to the end

SELECT * FROM newsDocs ORDER BY pin DESC, id DESC

Sql statement order by with condition?

SELECT *
FROM table
ORDER BY 
    CASE
        WHEN date(date) = curdate() THEN no * (-1) -- when date is current date, sort descending (ascending by negative ID, to reverse order)
        ELSE no -- else keep default ascending sort order
    END

Order by condition in T-SQL

You can use

order by case when lastdate is null then 0 
              else 1 end, username

How to order by an arbitrary condition in SQL

SELECT *
FROM   Bable
ORDER  BY CASE WHEN name LIKE '%$..' THEN 0 ELSE 1 END,
          Name 

• SQLFiddle Demo

How can one sort data in r based on a condition being met and time ordering?

A base R option using ave + subset

subset(
  df,
  ave(x == "yes", ID, FUN = cumsum) > 0
)

gives

  ID   x y
2  1 yes 2
3  1  no 3
4  1  no 4
7  2 yes 3
8  2  no 4

---

A data.table option following the same idea as above is

> setDT(df)[, .SD[cumsum(x == "yes") > 0], ID]
   ID   x y
1:  1 yes 2
2:  1  no 3
3:  1  no 4
4:  2 yes 3
5:  2  no 4

---

Related Topics