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
--
Nemanja Corlija <[EMAIL PROTECTED]>
