On 12/15/2010 1:34 PM, _ Robal _ wrote: > What I'm trying to do is to get the whole rows with unique lineID's > and biggest date value... kinda like versioning. > > ....but sqlite keeps returning just single row, no matter how many rows > should be returned. > > The statement I use goes as follows: > SELECT * FROM ( > SELECT * FROM lines ORDER BY date DESC > ) AS e > GROUP BY e.lineID
Works for me, I get two rows. Not the ones you expect though: nothing says that the engine must choose the first (or last, or any particular) row to represent the group. Try this: select * from lines l1 where not exists (select 1 from lines l2 where l1.lineID=l2.lineID and l1.date < l2.date); -- or select * from lines l1 where date = (select max(date) from lines l2 where l1.lineID = l2.lineID); -- or select * from lines l1 where ID = (select ID from lines l2 where l1.lineID = l2.lineID order by date desc limit 1); -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
