CODEWITHSUNDEEP
sqlpostgresqldateselectgreatest-n-per-group
Suyash
Suyash

Reputation: 341

PostGreSql Get Unique combination of two columns using Having Max Clause

I am using postGreSql. I have a currency conversion table which has following columns DateOfClosing, fromCurrency, toCurrency, closingRate. dateOfClosing is in varchar format I want to find latest unique combination of fromCurrency and toCurrency from past 5 days for example if table contents following

DateOfClosing    fromCurrency     toCurrency   closingRate
2020-06-25       INR              USD          1
2020-06-26       INR              USD          3
2020-06-26       JPY              USD          2
2020-06-24       THB              USD          1

It should return:

DateOfClosing    fromCurrency     toCurrency   cloisingRate
2020-06-26        INR             USD          3
2020-06-26        JPY             USD          2
2020-06-24        THB             USD          1

I tried using groupby with having max clause but getting error because of varchar to date conversion. Can someone give me a better solution?

Upvotes: 0

Views: 255

Answers (2)

GMB
GMB

Reputation: 222512

Use distinct on:

select distinct on (fromCurrency, toCurrenty) t.*
from mytable t
where dateOfClosing >= current_date - interval '5 day'
order by fromCurrency, toCurrenty, dateOfClosing desc

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521674

We can use DISTINCT ON here:

SELECT DISTINCT ON (fromCurrency, toCurrency) *
FROM yourTable
ORDER BY fromCurrency, toCurrency, DateOfClosing DESC;

Demo

Upvotes: 2

Related Questions