sqlite GROUP BY and ORDER-CodePudding

Is it possible to get value from column path (a string) where column status (string) is "REC", else if status="INFO", ... ?

SELECT status, count(idFileRemote), 
round(avg(rating), 2) AS rating, 
album, coverhash, path 
FROM tracks 
GROUP BY album

Ex: If I have

status   Album  Path
------   -----  -----
INFO     foo    path1
REC      foo    path2
OTHER    foo    path3

INFO     bar    path91
OTHER    bar    path93

I would get

status   Album  Path
REC      foo    path2
INFO     bar    path91

CodePudding user response：

You can utilize SQLite's bare columns to use conditional aggregation in the HAVING clause:

SELECT *
FROM tracks
GROUP BY Album
HAVING MIN(CASE status WHEN 'REC' THEN 1 WHEN 'INFO' THEN 2 ELSE 3 END);

See the demo.

Note that this problem would be typically solved with window functions, but I don't use them in my query because Android supports them only since API version 30.