CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
ayushgp
ayushgp

Reputation: 5101

How to get columns from multiple rows in a single row in SQL

I want to get 2 columns col_a and col_b's values for min and max of some other column. For example:

id last_updated col_a col_b
1 2021-01-01 abc xyz
1 2021-01-02 abc_0 xyz_0
1 2021-01-03 abc_1 xyz_1
1 2021-01-04 abc_2 xyz_2
2 2021-01-01 abc xyz
2 2021-01-01 abc xyz
...

I want to get the result:

|1|abc|abc_2|xyz|xyz_2|

That is the result of grouping by id, and getting the values of these columns while putting the condition of min and max on some other column(last_updated).

I came up with the following query:

select id, max(last_updated), min(last_updated)
from my_table
group by id

This gives me the id and min and max dates but not the other 2 columns. I'm not sure how to get the values for the other 2 columns for both dates in same query.

Upvotes: 1

Views: 1240

Answers (4)

Nidhin HS
Nidhin HS

Reputation: 26

select id,max(last_updated) last_updatedMax, min(last_updated) last_updatedMin,max(col_aMax) col_aMax, max(col_aMin) col_aMin,max(col_bMax) col_bMax, max(col_bMin) col_bMin
from
(
    select 
        *
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_aMax
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_aMin
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_bMax
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_bMin
     from my_table
) t
group by id

Upvotes: 0

Dale K
Dale K

Reputation: 27462

Not the neatest solution but demonstrates another way to obtain the data you want. We join the table on itself as we normally want data from 2 rows, then we use cross apply to restrict it to first and last.

select T1.id, T2.col_a, T1.col_a, T2.col_b, T1.col_b
from #my_table T1
inner join #my_table T2 on T1.id = T2.id
cross apply (
  select id, max(last_updated) MaxLastUpdated, min(last_updated) MinLastUpdated
  from #my_table
  group by id
) X
where T1.last_updated = X.MaxLastUpdated and T2.last_updated = X.MinLastUpdated;

With the sample data provided this appear to perform worse than the row_number() solution. The fastest solution is the analytical functions.

Upvotes: 2

Popeye
Popeye

Reputation: 35930

You can use MIN and MAX analytical function as follows:

select id,
       max(case when mindt = last_updated then col_a end) as min_col_a,
       max(case when maxdt = last_updated then col_a end) as max_col_a,
       max(case when mindt = last_updated then col_b end) as min_col_b,
       max(case when maxdt = last_updated then col_b end) as max_col_b
  from
(select t.*,
       min(last_updated) over (partition by id) as mindt,
       max(last_updated) over (partition by id) as maxdt
  from your_table t) t
group by id

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

We can use ROW_NUMBER, twice, to find the first and last rows, as ordered by last_updated, for each id group of records. Then, aggregate by id and pivot out columns for the various col_a and col_b values.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated) rn_min,
              ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated DESC) rn_max
    FROM yourTable
)

SELECT
    id,
    MAX(CASE WHEN rn_min = 1 THEN col_a END) AS col_a_min,
    MAX(CASE WHEN rn_max = 1 THEN col_a END) AS col_a_max,
    MAX(CASE WHEN rn_min = 1 THEN col_b END) AS col_b_min,
    MAX(CASE WHEN rn_max = 1 THEN col_b END) AS col_b_max
FROM cte
GROUP BY id;

Upvotes: 2

Related Questions