I'm new to SQL.
I have a query like
select * from mytable order by 1 desc limit 2
If mytable has only 1 row this query will return that row. How can I make sure my query always return 2 result and if mytable has 1 row it will return NULL
That's my 1st question here, sorry for my English
You could try using a union trick here:
SELECT col1, col2, col3
FROM
(
SELECT col1, col2, col3, 1 AS priority FROM mytable
UNION ALL
SELECT NULL, NULL, NULL, 2
UNION ALL
SELECT NULL, NULL, NULL, 2
) t
ORDER BY
priority,
col1
LIMIT 2;
The above strategy is to include via union two "empty" records. However, these empty records would only ever appear in the result set in the event that your table has fewer than 2 records.
If you have a bunch of columns and don't want to list them, you can use this variation on Tim's answer:
select t.*
from mytable t
union all
select t.*
from (select 1 as n union all select 2 as n) left join
mytable t
on 1 = 0; -- always false
order by col1 desc
limit 2;
The descending sort puts non-NULL values first. This assumes that col1 is not NULL -- so valid rows are first.
Notes: