SQLite User Forum

Query Syntax to Select Distinct Entries for a Maximum Value
Login

Query Syntax to Select Distinct Entries for a Maximum Value

(1) By anonymous on 2022-02-10 14:46:43 [link] [source]

I have the following table:

"CREATE TABLE IF NOT EXISTS daily( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," "value REAL," "date_id INTEGER NOT NULL," "symbol_id INTEGER NOT NULL," "FOREIGN KEY (symbol_id) REFERENCES sym_master (symbol_id)" ")"

I insert values :

INSERT INTO daily (value, date_id, symbol_id) VALUES (?,?,?)", [5.99, 1, 1])

The table looks like this:

value, date_id, symbol_id
[5.99, 1, 1]
[5.99, 2, 1]
[5.99, 3, 1]
.
.
.
[5.99, 1, 2]
[5.99, 2, 2]
[5.99, 17, 2]
.
.
.

The date_id will have a max value for every group of the symbol_id

-> lets say the max Value for every entry with symbol_id == 1 is 2314, and the max Value for all entries which have a symbol_id of 2 is 1326. There are many symbol_id 1 to n

i want to query the table, and for every unique symbol_id in it, getting the MAX(date_id) value, so i would return something like:

symbol_id Max(date_id) 1 2314 2 1326 3 . . . n` ...

SELECT DISTINCT symbol_id, MAX(date_id) FROM daily

gives me 'only' the MAX Value of the whole column, i would like to have it by group, can someone point me to the right approach?

(2.1) By Gunter Hick (gunter_hick) on 2022-02-10 14:57:59 edited from 2.0 in reply to 1 [link] [source]

Add GROUP BY 1 to your query.
Edit: no need for DISTINCT

(3) By anonymous on 2022-02-10 15:19:42 in reply to 2.1 [source]

thank you Gunter, i was not expecting to have it so simple, and yes true, there should be only 1 MAX date_id

so GROUP BY 1 groups all records according to the first column mentioned in the SELECT clause and then applies the MAX function individually to those groups I guess?