Check number of query after use limit SQL

user_13786545 0
Viewed 117

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

mysql sql
2 Answers

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.

user_18632290

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:

  • This makes it easy to add more rows (just change the subquery).
  • This makes it easy to handle more columns -- they are inserted automatically.
  • No subquery is needed.
user_11440350
Related