Selecting 5 rows from a table, but one row is specifically specified.

Question

Hi

This seems possible, but at the same time I am wondering if it actually is possible.

I have a table {users}. I want to pull 5 rows from that table, but at the same time, I want to specify one row.

The closest sql statement I get is:

SELECT 
    u.id, u.full_name, u2.id, u2.full_name
FROM 
    users u
JOIN 
    users u2 ON u2.id != 872
WHERE 
    u.id = 872
LIMIT 4

I know I can just do this in two sql statements, but I want to try and only do it in one.

Thank you

Jason246

Answer 1

SELECT  *
FROM    users
ORDER BY
        u.id = 872 DESC, RANDOM()
LIMIT 5

More efficiently, though, will be to make it in a UNION:

SELECT  *
FROM    users
WHERE   id = 872
UNION ALL
SELECT  *
FROM    (
        SELECT  *
        FROM    user
        WHERE   id <> 872
        LIMIT 4
        ) q