There is some magic in SQLite regarding non-aggregate columns in an aggregate query:
If there is a single MIN or MAX aggregate function in the GROUP BY, then the non-aggegate columns will be chosen from tha particular row SELECT id, MIN(source_id),value FROM test WHERE id IN (1,2) GROUP BY 1; Returns the value from the record with the smallest source_id within the group. -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Ivan Krylov Sent: Dienstag, 12. Februar 2019 12:13 To: [email protected] Subject: [EXTERNAL] [sqlite] Choosing one value from the group with priority Hi! I have a table of some values obtained from different sources: create table test ( id, source_id, value, primary key(id, source_id) ); insert into test values (1, 1, 11), (1, 2, 12), (1, 3, 13), (2, 1, 21), (3, 2, 32); When SELECTing the values, I would like to filter by id and get only one value per id. This part is misleadingly easy: select * from test where id in (1,2) group by id; but then I don't get to control which source I'm obtaining the values from (when there is more than one). Let's assume for now that I prefer to choose values with a particular source_id, but if those are not present, I would take what's available. I managed to invent a query which would assign a priority to each value using window functions: select *, row_number() over win from test where id in (1,2) window win as ( partition by id order by abs(source_id-3) ); By subtracting a different value in the ORDER BY ABS(...) clause, I can force a value with a different source_id to come up first. And since SQLite doesn't currently allow putting window functions in the WHERE clause, I'm using a nested query to actually get the values with the highest priority: select id, value from ( select id, value, row_number() over win as priority from test where id IN (1,2) window win as ( partition by id order by abs(source_id-3) ) ) where priority = 1; which results in the following query plan: QUERY PLAN |--CO-ROUTINE 1 | |--CO-ROUTINE 3 | | |--SEARCH TABLE test USING INDEX sqlite_autoindex_test_1 (id=?) | | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY | `--SCAN SUBQUERY 3 `--SCAN SUBQUERY 1 Is this the most effective way to express my query? Can the more general problem of assigning a priority to all sources (e.g. "I want records from source_id 3, otherwise 1, otherwise 2") be solved in a similar way? -- Best regards, Ivan _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
