1

I have a table with some columns, amongst which there are two - number, joining_date.
What I want to select is the newest joining date and matching number. I created the following script:

SELECT ac.number, ac.joining_date
FROM accounts ac
         INNER JOIN (
    SELECT number, MAX(joining_date) as maxDate FROM accounts GROUP BY number
) iac ON ac.number = iac.number AND ac.joining_date = iac.maxDate;

It seems fine, however, I have noticed that when the joining_date is equal i.e. 2020-04-02 10:17:00.000000 for more than one record, the number appears twice in the result, even if the MAX should return only one row.

Question: how to retrieve only one number by the newest joining_date? Is DISTINCT guarantees that?

Forin
  • 1,549
  • 2
  • 20
  • 44

2 Answers2

2

With the DISTINCT ON clause:

SELECT DISTINCT ON(number) number, joining_date
FROM accounts
ORDER BY number, joining_date DESC
forpas
  • 160,666
  • 10
  • 38
  • 76
  • I was thinking about that as well. But lets assume that I have third column `status`. Since distinct applies by the first number occurency, the latest status may be invalid. – Forin May 07 '20 at 09:30
  • 1
    The number of columns returned by the query is not relevant. With DISTINCT ON the result is exactly 1 row for each number and that row is the 1st row as it is fetched by the ORDER BY clause. – forpas May 07 '20 at 09:34
0

EDIT: I overlooked the postgres tag, rendering my answer not very useful.

Original answer:

There are several options, and I usually use CROSS APPLY:

SELECT ac.number, ac.joining_date
FROM accounts ac
CROSS APPLY (SELECT TOP 1 a.number, a.joining_date 
             FROM accounts a 
             WHERE a.number = ac.number AND a.joining_date = ac.joining_date
             ORDERBY a.joining_date DESC) iac

Another option is to use Row_Number() OVER (PARTITION BY a.number ORDER BY a.joining_date DESC) as rownum in the subquery, while also adding WHERE rownum=1 to ensure only one match.

Ayam
  • 169
  • 1
  • 12
  • Unfortunately, Postgres doesn't support CROSS APPLY. – Forin May 07 '20 at 08:52
  • Postgres follows the SQL standard, you would need to use `cross join lateral` there (instead of the non-standard `cross apply`) –  May 07 '20 at 09:08
  • @Ayam . . . Postgres does support lateral joins (what `cross apply` does). It uses a different syntax. – Gordon Linoff May 07 '20 at 11:53