Find the position of row and total rows with certain where and order by clauses

Question

I have a sql query with where and order by clauses:

SELECT "id" FROM "persons" 
WHERE "id" in (10,20,30,40,50,60,70,80,90,100) 
ORDER BY "id" DESC

Now I need to get the position of row with id=20 and total amount of found rows in above query. So I do

SELECT "id",pos FROM(
   SELECT "id", rownum() as pos FROM(
       SELECT "id" FROM "persons" 
       WHERE "id" in (10,20,30,40,50,60,70,80,90,100)
       ORDER BY "id" DESC
   )
)
WHERE "id"=20;

However, my last query only gives me the position of row. How can I now get and total amount of rows? Condition: without window functions as I use RDBMS which don't support them - h2 and hsqldb.

Answer 1

Try this one with HSQLDB

WITH p("id", "pos") AS ( SELECT "id", rownum() AS "pos" FROM "persons" 
   WHERE "id" IN (10,20,30,40,50,60,70,80,90,100)
   ORDER BY "id" DESC) 
SELECT "id", "pos", (select count(*) from P) AS "count" FROM P
WHERE "id"=20;