Return only one row of a select that would return a set of rows

Question

I have a table like:

create table myTab(
  id integer primary key,
  is_available boolean not null default true
);

I need to do a query that returns only the first encountered row that has is_available set to false.

Filter by is_available, order by id if that indicates the first entry and then take the first element only. — juergen d, Aug 10 '13 at 16:29
Too basic. Just read [the manual about `SELECT`](http://www.postgresql.org/docs/current/interactive/sql-select.html) — Erwin Brandstetter, Aug 10 '13 at 17:16

Roman Pekar · Accepted Answer · 2013-08-10T17:38:20.207

4

something like

select *
from myTab
where not is_available
order by id asc
limit 1

edited Aug 10 '13 at 17:38

answered Aug 10 '13 at 16:29

Roman Pekar

107,110
28
195
197

Unlike some other RDBMS Postgres has a proper `boolean` type. Use `is_available = FALSE` or `NOT is_available` instead. – Erwin Brandstetter Aug 10 '13 at 17:17
thanks @ErwinBrandstetter, it's obvious then PostgreSQL is not my most exp. DB :) But I like it very much – Roman Pekar Aug 10 '13 at 17:38

score 1 · Answer 2 · edited Aug 10 '13 at 17:28

1

Try out this ..

select id,is_available from myTab
where is_available = false
order by id asc
limit 1

If you want row from last inserted then go with this ..

    select id,is_available from myTab
    where is_available = false
    order by id desc
    limit 1

edited Aug 10 '13 at 17:28

Erwin Brandstetter

605,456
145
1,078
1,228

answered Aug 10 '13 at 17:17

Vijay

8,131
11
43
69

1

in my case, ordering by id isn't a bad solution, because i the id field involved in my query are a subset of all id contained in table (i have also another clause on WHERE) – giozh Aug 11 '13 at 09:22

score 1 · Answer 3 · answered Aug 10 '13 at 17:30

Alternatively, you could use NOT EXISTS to find the first tuple, in most cases this is the fastest solution, too:

SELECT *
FROM myTab mt
WHERE mt.is_available = False
AND NOT EXISTS (
    SELECT *
    FROM myTab nx
    WHERE nx.is_available = False
    AND nx.id < mt.id
    );

Return only one row of a select that would return a set of rows

3 Answers3