MySQL Order by In()

MySQL ORDER BY IN()

I think you may be looking for function FIELD -- while normally thought of as a string function, it works fine for numbers, too!

ORDER BY FIELD(field_name, 3,2,5,7,8,1)

Sort the rows according to the order specified in WHERE IN clause

You should use "ORDER BY FIELD". So, for instance:

SELECT * FROM table WHERE id IN (118,17,113,23,72) 
ORDER BY FIELD(id,118,17,113,23,72)

MySQL - ORDER BY values within IN()

SELECT id, name
FROM mytable
WHERE name IN ('B', 'A', 'D', 'E', 'C')
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

The FIELD function returns the position of the first string in the remaining list of strings.

However, it is much better performance-wise to have an indexed column that represents your sort order, and then sort by this column.

MySQL : ORDER BY expression?

You almost got it. A condition in MySQL returns 0 or 1. And since 0 comes before 1 you have to either sort desc

SELECT name,end_at FROM items 
ORDER BY (end_at IS NULL OR end_at > CURDATE()) DESC, 
         views DESC;

or negate the condition to get the desired result

SELECT name,end_at FROM items 
ORDER BY end_at IS NOT NULL AND end_at < CURDATE() ASC, 
         views DESC;

MySQL: Order by specific items first then by time

FIELD() is tricky for this, because it returns 0 if there are no matches. You can construct an expression that does what you want:

order by coalesce(nullif(field(id, 6, 3, 2), 0), 999999),
         created_at desc

If you know that the ids are always descending for the fixed values, then you can use:

order by (case when id in (6, 3, 2) then id end) desc,
         created_at desc

MYSQL - Order By Id In DESC Order, Group By X

You have misunderstood how GROUP BY works in SQL, due to a feature of MySQL. In standard SQL every non aggregate column in the SELECT statement MUST be in the GROUP BY clause (there is an exception for columns whose values are 100% dependent on a column already in the GROUP BY clause, although few flavours of SQL support this exemption).

MySQL does not enforce this by default, but which rows values are used for those columns is not defined. While you might get the one you want, you also might not. And even if you do there is a chance that it will change in the future.

The ordering is independent of the GROUP BY normally, although if you do not specify an ORDER clause then the results will be ordered based on what was required to perform the GROUPing (ie, if it helps to order the rows in one order to do the GROUP BY then MySQL will not bother to reorder the records afterwards unless you specifically tell it to with an ORDER BY clause).

So with your current data, grouping by ads_post_id the value of id that is returned could be 22, 23, 24, 104, 250, 253 or 767. Which one MySQL choses to use is not defined.

With your current data fixing this is trivial as you can just get the MAX id:-

SELECT ads_post_id, MAX(id) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

MAX will return 1 row for each GROUPed value.

The normal problem is that people want another column for that row. For example say that each of the rows in your sample data also had an IP address, and you wanted the one that equated to the highest id for the ads_post_id:-

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
22   | 983314845117571     192.168.0.0
23   | 983314845117571     192.168.0.5
24   | 983314845117571     192.168.0.7    
104  | 983314845117571     192.168.0.0
250  | 983314845117571     192.168.0.4
253  | 983314845117571     192.168.0.6
767  | 983314845117571     192.168.0.1     
---------------------------------------------------------------------------

In this case you cannot just use MAX. For example if you tried:-

SELECT ads_post_id, MAX(id), MAX(ip_address) 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

You would get the following data returned

id   | ads_post_id         ip_address
---------------------------------------------------------------------------
767  | 983314845117571     192.168.0.7     
---------------------------------------------------------------------------

If you tried the following in most flavours of SQL you would get an error. In MySQL with the default settings you would get a result, but which IP address is returned is not defined (and in effect random).

SELECT ads_post_id, MAX(id), ip_address 
FROM fb_ads 
GROUP BY ads_post_id 
LIMIT 6

The solutions to this are either to get the max id for each ads_post_id in a sub query and then joining that back to the table to get the rest of the values:-

SELECT a.ads_post_id,
        a.id,
        a.ip_address
FROM fb_ads a
INNER JOIN
(
    SELECT ads_post_id, MAX(id) AS max_id 
    FROM fb_ads 
    GROUP BY ads_post_id 
) sub0
ON a.ads_post_id = sub0.ads_post_id
AND a.id = sub0.max_id

An alternative is to (ab)use the GROUP_CONCAT aggregate function. GROUP_CONCAT will bring back all the values concatenated together into 1 field, each separated by a , (by default). You can add an ORDER BY clause to force the order they are concatenated into. The you can use SUBSTRING_INDEX to return everything up to the first comma.

This can be useful for simple data, but becomes problematic with text data or fields that max be NULL.

SELECT a.ads_post_id,
        SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY id DESC), ',', 1),
        SUBSTRING_INDEX(GROUP_CONCAT(ip_address ORDER BY id DESC), ',', 1)
FROM fb_ads 
GROUP BY ads_post_id

Sort by order of values in a select statement in clause in mysql

Actually, this is better:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

MySQL order by id first then by date

I've solved the problem by using "CASE" statement. Just in case someone find this useful.

    SELECT 
     campaignId, status, dateCreated, startDate, endDate 
   FROM campaign 
   WHERE 
       deleted='False' 
   ORDER BY 
       CASE WHEN endDate > CURDATE() THEN campaignId END DESC, 
       CASE WHEN endDate < CURDATE() THEN DATE(endDate) END DESC

MySQL Order by CASE

Just add a second level of sorting to your ORDER BY clause:

ORDER BY
    CASE WHEN cl.name = 'Nameone' THEN 0
         WHEN cl.name = 'Nametwo' THEN 1
         ELSE 2 END,
    cl.name            -- add this sort condition

Note that adding cl.name as a second sorting condition will have no effect for records where the name is Nameone or Nametwo, because the name is always the same for those records.