CODEWITHSUNDEEP
sqlsqlitemax
proninyaroslav
proninyaroslav

Reputation: 772

How can I get MAX() field in a sqlite table using additional condition?

There is the following table structure in sqlite:

==========================
| Id | Name | Date       |
==========================
| 1  | Foo  | 2021-01-01 |
--------------------------
| 2  | Foo  | 2021-01-02 |
--------------------------
| 3  | Foo  | 2021-01-03 |
--------------------------
| 4  | Bar  | 2021-01-01 |
--------------------------
| 5  | Bar  | 2021-01-02 |
==========================

and I need to implement the following logic in one query (pseudocode):

$names = 'SELECT name FROM MyTable'

for ($name in $names) {
  $fields = 'SELECT * FROM MyTable WHERE name = :name'
  $id = getIdWithBiggestDate($fields)
}

As a result, I should get something like this (items with the unique names and maximum dates):

==========================
| Id | Name | Date       |
==========================
| 3  | Foo  | 2021-01-03 |
--------------------------
| 5  | Bar  | 2021-01-02 |
==========================

Is it possible to do this in one query?

Upvotes: 1

Views: 137

Answers (2)

datosula
datosula

Reputation: 1586

select distinct Name,Date 
from MyTable
where 
(Name,Date) in (select Name,max(Date) from MyTable group by Name)

Upvotes: 0

forpas
forpas

Reputation: 164064

In SQLite you can do it with this non-standard SQL query:

SELECT Id, Name, MAX(Date) AS Date
FROM MyTable
GROUP BY Name

This works in SQLite because a column like Id which does not appear in the group by clause and also it is not aggregated (bare column) is returned from the row that contains the max Date.

See the demo.

Upvotes: 2

Related Questions