CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
Gargoyle
Gargoyle

Reputation: 10336

Count rows based on row value

I am calling a stored procedure that returns a table with two columns, an ident (integer) and a score (float4). The integer column will be unique values. I now want to know how many rows in the table have a larger/smaller score than the ident with a given value. I'm struggling to figure out how to do that in SQL. If it were something like PHP I'd sort the returned data by score, find the index of the row that has the ident I am looking for, and then subtract that from the total # of rows, for example. In PostgreSQL 9.1.15, I'm not sure how to do that.

SELECT COUNT(*) 
FROM my_stored_proc()
WHERE score > *Score of person with given ident*
ORDER BY score;

Upvotes: 2

Views: 448

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220952

Use window functions:

SELECT worse, better
FROM (
  SELECT 
    ident,
    COUNT(*) OVER (ORDER BY score ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) worse,
    COUNT(*) OVER (ORDER BY score ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) better,
  FROM my_stored_proc()
) t
WHERE ident = 2; -- replace with the "ident" you care about

This will simply count the number of rows in the result set that are above or below the current row if ordered by score.

Anyway, Gordon's solution might be slightly better, as it takes into account the possibility of an ident being returned more than once from your my_stored_proc(), and taking each ident's maximum score.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If you only care about ident = 2, you can do:

select sum(case when t.score < t2.score then 1 else 0 end) as LessThan,
       sum(case when t.score > t2.score then 1 else 0 end) as GreaterThan
from table t cross join
     (select t.* from table where ident = 2) t2;

If you only want to reference the table once (as you would if accessing it were expensive), you could do the above with a CTE or you could do:

select sum(case when score < score2 then 1 else 0 end) as LessThan,
       sum(case when score > score2 then 1 else 0 end) as GreaterThan
from (select t.*,
             max(case when ident = 2 then score end) over () as score2
      from table t
     ) t

Upvotes: 1

Related Questions