Nemanja Corlija wrote:
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?
Something like this perhaps?
select fname, vers, chng
from t1 first
where fname like 'file%' and not exists
(select * from t1 second
where first.fname=second.fname and first.vers < second.vers)
Igor Tandetnik
