CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Manu
Manu

Reputation: 1120

Using a subquery to find rows given a max value of date

I have the following table:

CREATE TABLE my_table 
(
    the_debt_id varchar(6) NOT NULL, 
    the_amount int NOT NULL, 
    the_debt_date date NOT NULL
)

INSERT INTO my_table
VALUES ('LMUS01', '200', '2/12/2019'), 
       ('LMUS01', '200', '2/11/2019'), 
       ('LMUS01', '300', '2/13/2019'), 
       ('LMUS02', '100', '2/10/2019'), 
       ('LMUS02', '150', '2/12/2019')

I want the rows from the max the_date grouped by the_id, in this case:

'LMUS01','300','2/13/2019'
'LMUS02','150','2/12/2019'

I tried the following query:

SELECT * FROM my_table 
WHERE the_debt_date = (SELECT MAX(the_debt_date) 
FROM my_table GROUP BY the_debt_id)

But the error I get is "more than one row returned by a subquery used as an expression". Please, any help you can give will be greatly appreciated.

Upvotes: 0

Views: 89

Answers (2)

Hamid Raza
Hamid Raza

Reputation: 43

SELECT * FROM my_table 
WHERE the_debt_date in (SELECT MAX(the_debt_date) 
FROM my_table GROUP BY the_debt_id)

Use in when subquery returns more then one records. Use = when you are 101% sure that subquery will always return only one record.

Upvotes: 1

GMB
GMB

Reputation: 222402

In Postgres, you can use handy extension distinct on for this:

select distinct on (the_id) * from my_table order by the_id, the_date desc

Upvotes: 2

Related Questions