SQL query for grouping by (project, environment) and finding the newest entry for each combination?-CodePudding

I have a table with data that looks like the following

project  | environment | timestamp
----------------------------------------
project1 | dev         | 1644515845
project1 | dev         | 1644513211
project1 | qa          | 1644515542
project2 | dev         | 1644513692
project2 | qa          | 1644514822

There are multiple projects and each project has multiple environments. There are multiple timestamps associated with each (project, environment) pair which correspond to the last time changes were made to the project.

Is there a query to group by (project, environment) and get the the entry with the newest timestamp for each combination of (project, environment)?

Something like

SELECT c.project, c.environment, c.timestamp
FROM c
GROUP BY c.project, c.environment, c.timestamp
ORDER BY c.timestamp

But it should only return one entry (the newest) for each (project, environment) tuple. My database is in a Cosmo DB if that makes a difference.

This is my expected output

project  | environment | timestamp
----------------------------------------
project1 | dev         | 1644515845
project1 | qa          | 1644515542
project2 | dev         | 1644513692
project2 | qa          | 1644514822

CodePudding user response：

You remove the timestamp from the group and selcet the MAX of thetomestamo

SELECT `project`, `environment`, MAX(`timestamp`)
FROM 
c
GROUP BY `project`, `environment`

here os a mysql sample to show that it works https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=86353790b7f73f766df01997255bdeb3