CODEWITHSUNDEEP
sqlpostgresql
Patrick Vibild
Patrick Vibild

Reputation: 360

Postgress query, group by, and show the latest timestamp

I am making queries on a single table that contains a timestamp.

My current query is the next one

SELECT MAX (time) as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
GROUP BY name, country, tocurrency, fromcurrency, exchangeratebuy;

How can I display the value of exchangeratebuy in this query without Gruoping by exchangeratebuy?

I want to make something like:

SELECT MAX (time) as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
GROUP BY name, country, tocurrency, fromcurrency;

But this gives me a error tha the selected column also need to be groupe by.

Upvotes: 0

Views: 230

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Use DISTINCT ON if you want one record per group:

SELECT DISTINCT ON (name, country, tocurrency, fromcurrency, exchangeratebuy)
       time as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
ORDER BY BY name, country, tocurrency, fromcurrency, exchangeratebuy, time DESC;

The "group" is defined by all the initial columns. The last column defines which row is taken from the group.

In your case, you may want just one row. For that, you can also use ORDER BY and LIMIT:

SELECT
       time as MostRecentDate, name, country, tocurrency, fromcurrency, exchangeratebuy
FROM margintest
WHERE country = 'DK' AND fromcurrency = 'DKK' AND tocurrency = 'EUR'
ORDER BY BY time DESC
LIMIT 1;

Upvotes: 1

Related Questions