PostgreSQL problem with the "Group By" Clause

user_2238704 0
Viewed 83

I have a table that looks something like this when ORDERED by R_Value Desc Note that it is important to understand that the below table is SQL SELECT ordered by R_Value

Id   Letter   R_Value
1     A         1500
2     A         1400
4     B         800
9     B         700
10    B         600
11    A         400
12    A         200

I want my result set to look like this. The result set needs to be grouped 3 times. Each time, the letter changes from A to B or vice versa, a new group should be formed.

Letter     Max_RValue
A           1500
B            800
A            400

I have already experimented with various things- lead/lag functions, partitioning, etc. and it seems impossible. A simple Group by(Letter) obviously won't work because all 'A' will be put in one group which is not what I want.

My next step would be to try this in a procedural language, dump it in an intermediate table and then read the results. Before doing that, is this even possible in Ordinary SQL?

postgresql-9.5 postgresql sql
2 Answers

You can use LAG() window function to check the previous value of Letter for each row and SUM() window function to create the groups of rows of consecutive occurrences.

Then aggregate in each group:

SELECT Letter, MAX(R_Value) Max_RValue
FROM (
  SELECT *, SUM(flag::int) OVER (ORDER BY R_Value DESC) grp
  FROM (
    SELECT *, Letter <> LAG(Letter, 1, '') OVER (ORDER BY R_Value DESC) flag
    FROM tablename
  ) t  
) t  
GROUP BY grp, Letter;

Or, simpler just select the rows where the Letter changes:

SELECT Letter, R_Value
FROM (
  SELECT *, LAG(Letter, 1, '') OVER (ORDER BY R_Value DESC) prev_Letter
  FROM tablename
) t
WHERE Letter <> prev_Letter;

See the demo.

user_104988280

Forpas's second solution is okay but it can be written more simply in Postgres as:

select letter, r_value
from (select t.*, lag(letter) over (order by r_value) as prev_letter
      from t
     ) t
where prev_letter is distinct from letter;
user_11440350
Related