CODEWITHSUNDEEP
snowflake-cloud-data-platform
Canovice
Canovice

Reputation: 10163

In snowflake query, filter table for 2nd largest value in one of its columns

We have the following query per In Snowflake, how to reference CTE in following table that filters using the max value in one of the table's columns:

with latest_track_metrics as (
   select * 
   from track_metrics
   qualify "week_id" = max("week_id") over()
)
select * from latest_track_metrics;

We are now attempting to query for the 2nd largest value in the week_id column. We've tried the following:

-- not all that close, (nth_value("week_id", 2) over(order by "week_id")) !== 2nd largest value.
select *
from track_metrics
qualify "week_id" = nth_value("week_id", 2) over(order by "week_id")

we've also tried:

with
    unique_values as (
        select distinct "week_id"
        from track_metrics
    )

select nth_value("week_id", 2) over(order by "week_id" desc) from unique_values

...which does return the 2nd largest value for week_id, however for some reason it returns an entire column (multiple rows) of that value, rather than just a single value. Something along this approach (getting unique values, and then grabbing the 2nd value in nth_value (descending)) makes sense. Perhaps there is an easier way to filter a table by the 2nd largest value in one of its columns?

Upvotes: 0

Views: 1566

Answers (3)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

So as mentioned by others, DENSE_RANK seems to be what you want:

with data(week_id, other_vals) as (
    select * from values
    (8, 1),
    (8, 2),
    (8, 3),
    (8, 4),
    (7, 5),
    (7, 6),
    (7, 7),
    (4, 8),
    (3, 9),
    (2, 10),
    (5, 11)
)
select *
from data
qualify dense_rank() over (order by week_id desc) = 2;

gives:

WEEK_ID OTHER_VALS
7 5
7 6
7 7

Upvotes: 1

Asim Rout
Asim Rout

Reputation: 103

Please check if this works for you:

create table track_metrics(week_id INT);
insert into track_metrics values(8), (8), (8), (8), (7), (7), (7), (4), 
(3), (2), (5);

 with unique_values as
       ( select * from track_metrics 
           group by week_id)
 select week_id, row_number() over (order by week_id desc) as 
 row_num 
 from unique_values qualify row_num=2;

Upvotes: 0

Dave Welden
Dave Welden

Reputation: 1918

Qualify is your friend again

select * 
   from track_metrics
   qualify row_number() over (order by "week_id" desc) = 2;

With duplicate values, to select the 2nd set of duplicates you would use DENSE_RANK()

select * 
   from track_metrics
   qualify dense_rank() over (order by "week_id" desc) = 2;

Upvotes: 3

Related Questions