Hi, Most databases I tested don't support RANK(). I know PostgreSQL supports it, and Oracle, and probably MS SQL Server. But MySQL, Apache Derby, HSQLDB, SQLite, and H2 don't support it. I thought about a workaround, and the easiest one I found is below. I guess it's not very efficient however. For MySQL and H2, it would be more efficient to use variables (@rank := ...).
drop table test; create table test(id int, c int); create index idx_test_id_c on test(id, c); insert into test values (1, 20), (1, 20), (1, 30), (1, 30), (1, 40), (2, 10), (2, 20), (2, 30), (2, 40), (2, 50), (3, 10); select id, c, rank from (select id, c, (select 1 + count(*) from test t2 where t2.id = t.id and t2.c > t.c) rank from test t) t where rank <= 2 order by id, c desc; select id, c, rank from ( select id, c, rank() over(partition by id order by c desc) rank from test) t where rank <= 2 order by id, c desc; Regards, Thomas -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
