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 ASC
sort 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 sameSELECT
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:
LEFT JOIN
is used; this will return ALL rows fromTable1
, regardless of whether or not there is a matching row inTable2
.The
WHERE t2.ID IS NULL
clause; this will restrict the results returned to only those rows where the ID returned fromTable2
is null - in other words there is NO record inTable2
for that particular ID fromTable1
.Table2.ID
will be returned as NULL for all records fromTable1
where the ID is not matched inTable2
.
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
Generating Xml File from SQL Server 2008
SQL Server Query for Many to Many Relationship
Rolling Sum Previous 3 Months SQL Server
Varchar Requires a Length When Rendered on MySQL
Oracle: Similar to Sysdate But Returning Only Time and Only Date
Left Join VS. Multiple Select Statements
SQL Server Split Comma Separated Values into Columns
SQL Query to Get Recursive Count of Employees Under Each Manager
What Is the Most Elegant Way to Store Timestamp with Nanosec in Postgresql
In SQL, What Is the Letter After a Table Name in a Select Statement
SQL Not a Single Group Group Function Error
How to Format Datetime as M/D/Yyyy in SQL Server
Postgres 9.4 JSONb Array as Table