CODEWITHSUNDEEP
sqliteselect
adam.baker
adam.baker

Reputation: 1485

Select a particular row in when there are multiple options

This is a query to select rows from a table. The table is such that there are potentially many rows with the particular (WritingSystem,InterpretationId) pairing.

select Form,WritingSystem,_id from TextForms where InterpretationId=?;

I want the result set to include (at most) one row for each WritingSystem, and I want it to be row with the smallest value of length(Form). I can't work out how to do this without some ugly sort of join.

This query would of course just return one row, whereas I want multiple rows where there are multiple values of WritingSystem.

select Form,WritingSystem,_id from TextForms where InterpretationId=? limit 1;

This query correctly returns one row per value of WritingSystem, but the selection of which row is arbitrary so far as I can see.

select Form,WritingSystem,_id from TextForms where InterpretationId=? group by WritingSystem;

Upvotes: 0

Views: 32

Answers (1)

CL.
CL.

Reputation: 180020

When you are using GROUP BY, you must specify how the output value are computed for each group. This is usually done with aggregation functions like MIN/MAX/COUNT/SUM.

In SQLite 3.7.11 and later, when you're using MIN/MAX, other columns like _id are guaranteed to come from the same row that matches the MIN:

SELECT Form, WritingSystem, _id, MIN(LENGTH(Form))
FROM TextForms
WHERE InterpretationId = ?
GROUP BY WritingSystem

Upvotes: 1

Related Questions