How to make conditional LIMIT in SELECT query?

Question

I want to make conditional order by with limit and offset. If the count is 1 then I want the latest date otherwise I want latest but one record. I was trying the below query,

If the count of a select query is 1 then,

Select * from table_name
ORDER BY date_column LIMIT 1;

Otherwise,

Select * from table_name
ORDER BY date_column LIMIT 1 OFFSET 1;

Is there any other way to implement the logic?

score 0 · Answer 1 · answered Nov 04 '20 at 17:08

The answer that is closest to your current code is to wrap it in a CASE WHEN statement. If you see it as CASE WHEN (1) THEN (2) ELSE (3), the logic speaks for itself. (2) is executed if (1) is true, else (3) is executed.

SELECT *
FROM table_name
WHERE CASE WHEN (
    SELECT COUNT(*)
    FROM table_name t
  ) > 1 THEN date_column = (
    SELECT date_column
    FROM table_name t
    ORDER BY date_column LIMIT 1 OFFSET 1
  ) ELSE date_column = (
    SELECT date_column
    FROM table_name t
    ORDER BY date_column LIMIT 1
  )
LIMIT 1

score 0 · Answer 2 · answered Nov 04 '20 at 17:11

0

You could use window functions:

select *
from (
    select t.*, 
        row_number() over(order by date_column) rn,
        count(*) over() cnt
    from mytable t
) t
where rn = 2 or cnt = 1

answered Nov 04 '20 at 17:11

GMB

216,147
25
84
135

Note: DESC (last but one) – wildplasser Nov 04 '20 at 17:13
@wildplasser: yes I asked myself the same question - the description says so, but the queries in the question show otherwise... – GMB Nov 04 '20 at 17:15
Agreed, the discription is vague. – wildplasser Nov 04 '20 at 17:17

score 0 · Accepted Answer · answered Nov 04 '20 at 17:31

0

select * from (
    Select * from table_name
    ORDER BY date_column LIMIT 2
 ) as foo ORDER BY date_column DESC LIMIT 1

answered Nov 04 '20 at 17:31

jjanes

37,812
5
27
34

score 0 · Answer 4 · answered Apr 28 '21 at 13:03

(SELECT * FROM table_name ORDER BY date_column OFFSET 1 LIMIT 1)
UNION ALL
(SELECT * FROM table_name ORDER BY date_column LIMIT 1)
LIMIT 1

If there are two or more qualifying rows, the second SELECT will be never executed (you can see that in the output of EXPLAIN ANALYZE). So, typically, this is the fastest possible way.

LIMIT 1 in the subqueries is logically redundant, but it helps Postgres to chose the most efficient query plan.

See:

How to make conditional LIMIT in SELECT query?

4 Answers4