getting the last occurence of a certain item in a sql request-CodePudding

I am trying to write sql requests to my database in order to get the last occurence of a certain item, here's what I mean illustrated by an example:

pigc=# SELECT date FROM msr_history WHERE pos = 'DV' AND msr_id = 177;
            date            
----------------------------
 2018-06-20 08:04:09.724103
 2018-06-20 08:09:49.484921
(2 rows)

The first line of my example is my sql request and I am trying to get only the last date, in this example it would be this one: 2018-06-20 08:09:49.484921 Note that the number of dates can differ so I can't just manualy select the second date each time. Thank you for your help.

CodePudding user response：

Use select max(column) to retrieve the highest value for that specific data type for all rows included in the where clause.

In this instance, no aggregation is required since no non-aggregated columns are being retrieved.

See https://www.postgresql.org/docs/9.5/functions-aggregate for a complete list of aggregate functions and specifically https://www.postgresqltutorial.com/postgresql-max-function

CodePudding user response：

The fastest way to achieve it to use the LIMIT clause in your query. This will prevent using aggregate function in your query and will run fastest -

SELECT date
  FROM msr_history
 WHERE pos = 'DV'
   AND msr_id = 177
 ORDER BY date DESC
 LIMIT 1;