Nemanja Corlija wrote:
Hi all,
I guess this isn't really all that complex, but I just can't think of
a query that does what I need.
Here's an example table:
CREATE TABLE t1 (fname TEXT, vers TEXT, chng INT, UNIQUE(fname,vers));
INSERT INTO t1 VALUES('file1', '1', 0);
INSERT INTO t1 VALUES('file1', '2', 1);
INSERT INTO t1 VALUES('file1', '3', 2);
INSERT INTO t1 VALUES('file2', '1', 0);
SELECT fname, MAX(vers), chng FROM t1
WHERE fname LIKE 'file%'
GROUP BY fname
Above query returns:
fname max(vers) chng
----------------------
file2 1 0
file1 3 0
fname and vers returned are OK, but chng is not. I need to have latest
chng in each group.
How can I get chng form the row with highest rowid in the group?
Can I even influence the order of records in a group?
AFAIK ORDER BY is done after GROUP BY so it can't be used here. HAVING
operates on the group but how do I put it to use here?
This needs to work on SQLite 2.
Thanks
If this worked:
select fname, chng from t1
where fname like 'file%'
group by fname
having vers = max(vers)
it would be great, but I think the SQL standard does not allow for any
column to appear in the SELECT results that is not an aggregate
function, or is not in the GROUP BY clause. It's probably not allowed
because it is ambiguous in some way that I don't see, but this should
work for you:
select fname, chng
from t1
inner join (select fname, max(vers) as maxvers from t1 where fname
like 'file%' group by fname) maxvers on
t1.fname = maxvers.fname and t1.vers = maxvers.maxvers
John
