Skip to main content
45 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 ...
Evan Carroll's user avatar
  • 63.8k
29 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 ( ...
David Browne - Microsoft's user avatar
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 ...
Erwin Brandstetter's user avatar
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 ...
Martin Smith's user avatar
  • 85.4k
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;
Renato Afonso's user avatar
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....
McNets's user avatar
  • 23.8k
16 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 ...
Mabu Kloesen's user avatar
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 ...
amacvar's user avatar
  • 709
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 ...
Erwin Brandstetter's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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 ...
ypercubeᵀᴹ's user avatar
13 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 ...
Erwin Brandstetter's user avatar
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 &...
SwapnilBhate's user avatar
13 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 ...
Erwin Brandstetter's user avatar
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 ...
ypercubeᵀᴹ's user avatar
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' ...
Lamak's user avatar
  • 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 ...
Dan Guzman's user avatar
  • 28.3k
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 ...
SqlZim's user avatar
  • 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 (...
Lennart - Slava Ukraini's user avatar
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 ...
Vérace's user avatar
  • 30.2k
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 ...
wBob's user avatar
  • 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, ...
Andriy M's user avatar
  • 23.1k
7 votes

Get second highest value in a table

You have the usual top trick such as: select top 1 * from ( select top 2 * from my_table order by value desc ) t order by value asc Or you can also use CTE like: with CTE as ( ...
irimias's user avatar
  • 1,931
7 votes

Select a distinct row

The row: Sandy 2452356 24523 is preferred because it has the largest o_id. You can pick the largest o_id per salesperson via: select max(o_id), salesperson from T group by salesperson ...
Lennart - Slava Ukraini's user avatar
7 votes

How to make DISTINCT ON faster in PostgreSQL?

Try the classic way: create index idx_station_logs__station_id on station_logs(station_id); create index idx_station_logs__submitted_at on station_logs(submitted_at); analyse station_logs; with t ...
Abelisto's user avatar
  • 1,559
7 votes

Get rows with most recent date for each different item

I understand that you want all the rows with the latest date for every product (ties included, i.e. all rows with the last date). This can be done with rank() function: select id, product_id, ...
ypercubeᵀᴹ's user avatar
7 votes
Accepted

How to get the greatest element of a GROUP BY?

Probably easiest to do with a join against a derived table: select a.* from PurchaseOrders as a join ( select sku, max(date) as date from PurchaseOrders group by sku ) as b on a....
Lennart - Slava Ukraini's user avatar
7 votes

PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

first_last_agg There is the additional module first_last_agg to make this simple. It's available from apt.postgresql.org (among others). Read instructions in the Postgres Wiki. Install it once per ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Selecting one row from each group based on a Varchar field

You can do something like this: select * from ( select *, row_number() over (partition by Avenger order by Type) as RN ) X where RN = 1 This will number the rows for each avenger, and for your ...
James Z's user avatar
  • 2,219

Only top scored, non community-wiki answers of a minimum length are eligible