I have a table that looks as follows:

which needs to be aggregated into one row, looking like this:

I understand that it needs to be grouped based on col2. But then what SQL syntax do I need to say that:

• value of col3 for lowest col1 value stays in col3
• value of col5 for lowest col1 value goes to col4
• value of col5 for second col1 value stays in col5
• value of col5 for highest col1 value goes to col6
• value of col7 for lowest col1 value stays in col7

CodePudding user response：

Think of this as three different select statements which isolate your min, max and secondary values from Col1. e.g. Select...Where Col1 = 1 (or whatever the min value is), Select...Where Col1 = 2, Select...Where Col1 = max.

Once you have those values broken out you can join all of them together and use the appropriate columns.

I chose to use min, max and row_number in the sub-query because I wasn't sure if your min is always 1 and your secondary value is always 2. If they are, you could write this a bit differently.

select 
    max(Case When a.col1 = mn.MinCol1 Then  a.col1 End) as NewCol1
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col2 End) as NewCol2
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col3 End) as NewCol3
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col5 End) as NewCol4
    ,max(Case When a.col1 = sec.SecondCol1 Then  a.col5 End) as NewCol5
    ,max(Case When a.col1 = mx.MaxCol1 Then  a.col5 End) as NewCol6
    ,max(Case When a.col1 = mn.MinCol1 Then  a.col7 End) as NewCol7
FROM AggTable a
--Get Min Values (Grouped By col2)
LEFT JOIN (Select 
                min(col1) as MinCol1 
                ,col2
          From AggTable
          Group By 
                col2
          ) mn on a.col1 = mn.MinCol1 and a.col2 = mn.col2
--Get Max Values (Grouped By col2)
LEFT JOIN (Select 
                max(col1) as MaxCol1 
                ,col2
            From AggTable
            Group By
                col2
            ) mx on a.col1 = mx.MaxCol1 and a.col2 = mx.col2 
--Get Second Value (Grouped By col2)
LEFT JOIN (select 
            row_number() OVER(Order By col1) as RowNumber
            ,col1  as SecondCol1
            ,col2
          From AggTable
          Group By
                col1
                ,col2
                ) sec on a.col1 = sec.SecondCol1 and a.col2 = sec.col2 and sec.RowNumber = 2

CodePudding user response：

Yet another option is using window functions, given that your DBMS allow them, whereas:

• FIRST_VALUE is in charge of gathering the first value of "col3" by ordering on "col1"
• LEAD is in charge of gathering the consecutive values of "col5" by ordering on "col1".

Then you retrieve only the first row with the LIMIT 1 clause.

SELECT col1,
       col2,
       FIRST_VALUE(col3) OVER(ORDER BY col1) AS col3,
       col5                                  AS col4,
       LEAD(col5, 1) OVER(ORDER BY col1)     AS col5,
       LEAD(col5, 2) OVER(ORDER BY col1)     AS col6,
       col7
FROM tab
LIMIT 1