Select First Record If None Match

Select first record if none match

I would like to select a row based on some criteria, but if no row
matches the criteria, I would like to return the first row

Shorter (and correct)

You don't actually need a WHERE clause at all:

SELECT street, zip, city
FROM address
ORDER BY street !~~ 'Test%', ord
LIMIT 1;

!~~ is just the Postgres operator for NOT LIKE. You can use either. Note that by inverting the logic (NOT LIKE instead of LIKE), we can now use default ASCsort order and NULLs sort last, which may be important. Read on.

This is shorter (but not necessarily faster). It is also subtly different (more reliable) than the currently accepted answer by @Gordon.

When sorting by a boolean expression you must understand how it works:

  • Sorting null values after all others, except special

The currently accepted answer uses ORDER BY <boolean expression> DESC, which would sort NULLs first. In such a case you should typically add NULLS LAST:

  • PostgreSQL sort by datetime asc, null first?

If street is defined NOT NULL this is obviously irrelevant, but that has not been defined in the question. (Always provide the table definition.) The currently accepted answer avoids the problem by excluding NULL values in the WHERE clause.

Some other RDBMS (MySQL, Oracle, ..) don't have a proper boolean type like Postgres, so we often see incorrect advice from people coming from those products.

Your current query (as well as the currently accepted answer) need the WHERE clause - or at least NULLS LAST. With the different expression in ORDER BY neither is necessary.

More importantly, yet, if multiple rows have a matching street (which is to be expected), the returned row would be arbitrary and could change between calls - generally an undesirable effect. This query picks the row with the smallest ord to break ties and produces a stable result.

This form is also more flexible in that it does not rely on the existence of a row with ord = 0. Instead, the row with the smallest ord is picked either way.

Faster with index

(And still correct.)
For big tables, the following index would radically improve performance of this query:

CREATE INDEX address_street_pattern_ops_idx ON address(street text_pattern_ops);

Detailed explanation:

  • PostgreSQL LIKE query performance variations

Depending on undefined details it may pay to add more columns to the index.

The fastest query using this index:

(
SELECT street, zip, city
FROM address
WHERE street LIKE 'Test%'
ORDER BY ord -- or something else?
-- LIMIT 1 -- you *could* add LIMIT 1 in each leg
)
UNION ALL
(
SELECT street, zip, city
FROM address
ORDER BY ord
-- LIMIT 1 -- .. but that's not improving anything in *this* case
)
LIMIT 1

BTW, this is a single statement.

This is more verbose, but allows for a simpler query plan. The second SELECT of the UNION ALL is never executed if the first SELECT produces enough rows (in our case: 1). If you test with EXPLAIN ANALYZE, you'll see (never executed) in the query plan.

Details:

  • Way to try multiple SELECTs till a result is available?

Evaluation of UNION ALL

In reply to Gordon's comment. Per documentation:

Multiple UNION operators in the same SELECT statement are evaluated
left to right, unless otherwise indicated by parentheses.

Bold emphasis mine.

And LIMIT makes Postgres stop evaluating as soon as enough rows are found. That's why you see (never executed) in the output of EXPLAIN ANALYZE.

If you add an outer ORDER BY before the final LIMIT this optimization is not possible. Then all rows have to be collected to see which might sort first.

How to select rows with no matching entry in another table?

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

  1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

  2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

Select the first matching row

Core Question:

SELECT prod, MIN(cust)
FROM yourTable
GROUP BY prod

For the "Bonus":

SELECT T.prod,
T.cust,
YC.SomeCustomerAttribute1,
YC.SomeCustomerAttribute2
FROM (
SELECT prod, MIN(cust) AS first_cust
FROM yourProducts
GROUP BY prod
) AS T
JOIN yourCustomers AS YC ON YC.cust = T.first_cust

Select data where condition matches and if none, then select all?

declare @tab table (id int , value varchar(10))
declare @id int = 4

insert into @tab
select 1,'Ajay'
union all
select 2,'Ajay1'
union all
select 3,'Ajay2'
union all
select 4,'Ajay3'
union all
select 5,'Ajay4'

select * from @tab
where id = case when exists (select * from @tab where id = @id) then @id else id end

Get top first record from duplicate records having no unique identity

The answer depends on specifically what you mean by the "top 1000 distinct" records.

If you mean that you want to return at most 1000 distinct records, regardless of how many duplicates are in the table, then write this:

SELECT DISTINCT TOP 1000 id, uname, tel
FROM Users
ORDER BY <sort_columns>

If you only want to search the first 1000 rows in the table, and potentially return much fewer than 1000 distinct rows, then you would write it with a subquery or CTE, like this:

SELECT DISTINCT *
FROM
(
SELECT TOP 1000 id, uname, tel
FROM Users
ORDER BY <sort_columns>
) u

The ORDER BY is of course optional if you don't care about which records you return.

How to select only the first rows for each unique value of a column?

A very simple answer if you say you don't care which address is used.

SELECT
CName, MIN(AddressLine)
FROM
MyTable
GROUP BY
CName

If you want the first according to, say, an "inserted" column then it's a different query

SELECT
M.CName, M.AddressLine,
FROM
(
SELECT
CName, MIN(Inserted) AS First
FROM
MyTable
GROUP BY
CName
) foo
JOIN
MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

How to get First and Last record from a sql query?

[Caveat: Might not be the most efficient way to do it]:

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1)

UNION ALL

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC
LIMIT 1)

How to execute a 2nd query if the first one returns nothing?

For multiple rows

WITH fast AS (
SELECT column1, column2
FROM mytable
WHERE <1st SET OF complex conditions>
)
, slow AS (
SELECT column1, column2
FROM mytable
WHERE NOT EXISTS (TABLE fast)
AND <2nd SET OF complex conditions>
)
TABLE fast
UNION ALL
TABLE slow;

Test with EXPLAIN ANALYZE: you'll see (never executed) for the second query (CTE slow) if the first one (CTE fast) returns any rows. I think that's what you are aiming for.

TABLE fast is just (standard SQL) short syntax for SELECT * FROM fast. See:

  • Is there a shortcut for SELECT * FROM?

Simpler for a single returned row

If both queries can only return a single row (or nothing), there is a simple, efficient solution without CTEs:

(
SELECT column1, column2
FROM mytable
WHERE <1st SET OF complex conditions>
)
UNION ALL
(
SELECT column1, column2
FROM mytable
WHERE <2nd SET OF complex conditions>
)
LIMIT 1;

Related:

  • Select first record if none match
  • Way to try multiple SELECTs till a result is available?


Related Topics



Leave a reply



Submit