44
votes
Accepted
In PostgreSQL, is there a type-safe first() aggregate function?
DISTINCT ON()
Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)
SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of ...
- 62.2k
30
votes
Accepted
SQL Server - Select most recent record from each group when performance is critical
If you have a small-enough number of (StationID, ParameterID) pairs, then try a query like this:
select StationID, ParameterID, m.DateTime LastDate
from StationParameter sp
cross apply
(
...
- 45.9k
27
votes
Accepted
How to make DISTINCT ON faster in PostgreSQL?
For only 400 stations, this query will be massively faster:
SELECT s.station_id, l.submitted_at, l.level_sensor
FROM station s
CROSS JOIN LATERAL (
SELECT submitted_at, level_sensor
FROM ...
- 172k
24
votes
Get second highest value in a table
To get the second highest distinct value in the table you can use
SELECT MIN(value)
FROM (SELECT DISTINCT TOP (2) value
FROM tablename
ORDER BY value DESC)T
/*If only one ...
- 83.3k
23
votes
Accepted
Get second highest value in a table
Assuming the highest value only occurs once, another way would be to use OFFSET (SQL Server 2012 or later):
SELECT *
FROM tablename
ORDER BY column DESC
OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY;
- 453
21
votes
Accepted
Select ONE most recent post for each author
If you aim is to have queries with maximum efficiency, none of the above queries is really the best. Not always at least.
Efficiency depends on many different things, like the specific DBMS, the ...
- 97.4k
20
votes
Accepted
get only rows with max group value
First answer uses a CTE to select max(group_value) and then join with the table.
with maxgv as
(
select element, max(group_value) maxg
from x
group by element
)
select x.element, x....
- 23.6k
16
votes
Accepted
Get rows with most recent date for each different item
Skinning Q#1 independently and slightly differently than @ypercube
with cte as (select row_number() over (partition by product_id,
invoice_id
...
- 679
15
votes
In PostgreSQL, is there a type-safe first() aggregate function?
Yay, I've found out an easy way with your case by using some features in PostgreSQL 9.4+
Let's see this example:
select (array_agg(val ORDER BY i))[1] as first_value_orderby_i,
(array_agg(val ...
- 251
14
votes
Accepted
Optimise a LATERAL JOIN query on a big table
Preliminary notes
You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would ...
- 172k
14
votes
Accepted
What is the optimum method of finding the per-item earliest date from a clustered index
This is related to "Index Skip Scan" optimization (see the Connect item below, from 2011). Unfortunately it has been closed as "Won't Fix".
Some related enhancements are already there but only for ...
- 97.4k
14
votes
Accepted
Left join without duplicate rows
Unless you are in a very old version of Postgres, you don't need the double join. You can get the same result by using a LATERAL join.
The duplicate results can be avoided in your method by adding a ...
- 97.4k
13
votes
Get second highest value in a table
A generic solution can be like below:
;WITH CTE AS
(
SELECT
Col1
, Col2
, <AnyColumns>
, ROW_NUMBER() OVER (ORDER BY <AnyColumns>) AS RowNum
FROM &...
- 504
12
votes
Accepted
Select rows based on a minimum in one column
Use DISTINCT ON:
SELECT DISTINCT ON (gid)
id, gid, distance, time_interval
FROM dis
ORDER BY gid, time_interval; -- more expressions to break ties?
Detailed explanation:
Select first row ...
- 172k
11
votes
The fastest way to get the most recent records
Self-joins seem cheap at low row counts, but I/O is exponential as the row count increases. I would prefer to solve this the CTE way, unless you are on SQL Server 2000 (please always specify the ...
- 180k
10
votes
Accepted
Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)
You can number and order id by us_state using the ROW_NUMBER() Window Function and only keep the n first values:
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id)...
- 10.1k
10
votes
The fastest way to get the most recent records
The greatest-n-per-group tag has a number of questions and answers relevant to this type of problem, with the canonical example for SQL Server being:
Retrieving n rows per group
With the two main ...
- 83k
10
votes
Accepted
Get the last 5 distinct values for each ID
There are probably many ways to do this. The first that comes to mind is to use window functions:
SELECT
id, postcode
FROM
( SELECT id, postcode,
ROW_NUMBER() OVER (PARTITION BY id
...
- 97.4k
10
votes
Accepted
Get ID of row in aggregate
You can use window (ranking). functions for this:
WITH ct AS
( SELECT X, Y, RowAddedDate, ID,
Rn = DENSE_RANK() OVER (PARTITION BY X, Y
ORDER BY ...
- 97.4k
10
votes
Use GROUP BY and return more than 2 columns
You can use ROW_NUMBER:
with cte as
(
select *,
RN = ROW_NUMBER() OVER(PARTITION BY unid ORDER BY cdts2 DESC)
from un_hi
where ag_id = 'bfd'
and cdts > '201708'
...
- 2,576
10
votes
Accepted
Can I combine SELECT TOP() with the IN operator?
One method is with CROSS APPLY:
SELECT top_50.ColumnName
FROM (VALUES(1),(2),(3)) AS id_list(ID)
CROSS APPLY(SELECT TOP(50) t.ColumnName
FROM TableName AS t
WHERE t.ID = id_list.ID
...
- 27.8k
10
votes
Accepted
SELECT DISTINCT ON, ordered by another column
You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See:
Get distinct on one column, order by another
PostgreSQL DISTINCT ON with different ORDER BY
SELECT *
FROM ...
- 172k
8
votes
Partition by ascending and descending performance
Example table and index
CREATE TABLE dbo.Test
(
a integer NOT NULL,
b integer NOT NULL,
c integer NOT NULL,
d integer NOT NULL,
e integer NOT NULL
);
CREATE INDEX i1 ON dbo.Test (...
- 83k
8
votes
Get The Most Recent Record In A Table By Date And User
There are many ways to do this. Here are some of them:
common table expression with row_number() version:
with cte as (
select *
, rn = row_number() over (
partition by ...
- 2,420
8
votes
Accepted
Limit left join for multiple rows
What version of MySQL are you using? MySQL 8 supports LATERAL which you need to be able to refer to i from the derived table:
SELECT i.*, si.child, si.parent_id
FROM items as i
LEFT JOIN LATERAL (...
8
votes
SELECT DISTINCT ON, ordered by another column
This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Window functions (see below) are well worth studying.
Nowadays, it is typically solved by using ...
- 29.5k
7
votes
Accepted
Exists subselect vs inner join?
The purpose of your subquery as I understand it:
Select rows where the latest related entry in billing_pricequotestatus has a qualifying name.
Incorrect 2nd query
It's not immediately clear to me why ...
- 172k
7
votes
Accepted
Efficiently get the last record
Where appropriate, the database engine can do a backwards scan, ie go straight to the end of the index and work backwards so it's nice and efficient. You can see this on your query execution plans ...
- 10.4k
7
votes
Accepted
multiple rows with the same maximum value
Yours appears to be a "greatest N per group" problem. What you can do is get the maximum seats per engine results:
SELECT
engines,
MAX(seats) AS max_seats
FROM
planes
GROUP BY
engines
and, ...
- 22.8k
Only top scored, non community-wiki answers of a minimum length are eligible