CODEWITHSUNDEEP
sqlsql-server
Derek Frank
Derek Frank

Reputation: 25

Pull records with unique flags in an efficient way

From the table below, I want to write a query that extracts the records where the flag first occurs. As an example, from the table below, I would want to pull the Nov 8 record, Dec 6 record, and Jan 10 record into a separate table. Any thoughts on how to best approach this? I'm not tied to having the flag column being a count - ideally it could be binary, but I'm not sure... the flag column is computed and not part of the raw data.

Date     Location KPI  Flag
11/8/2017   A      5      1
11/15/2017  A      5      1
11/22/2017  A      5      1
11/29/2017  A      5      1
12/6/2017   A     10      2
12/13/2017  A     10      2
12/20/2017  A     10      2
12/27/2017  A     10      2
1/3/2018    A     10      2
1/10/2018   A     15      3
1/17/2018   A     15      3
1/24/2018   A     15      3

Upvotes: 0

Views: 58

Answers (3)

RogerSK
RogerSK

Reputation: 393

So far what I understand is to exact the most older date from each of the flag categories.

select * from (
    select
        Date,
        Location,
        KPI,
        Flag
        row_number() over(partition by Flag order by Date asc) as RN
    from
        Your_Table
) t
where t.RN = 1

This solution is using partition to get the expected data.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Often the fastest method is a correlated subquery:

select t.*
from t
where t.date = (select min(t2.date)
                from t t2
                where t2.location = t.location and
                      t2.kpi = t.kpi
               );

In particular, this can make use of an index on (location, kpi, date).

That said, if you want the rows where kpi changes, then you might want lag():

select t.*
from (select t.*,
             lag(kpi) over (partition by location order by date) as prev_kpi
      from t
     ) t
where prev_kpi is null or prev_kpi <> kpi;

In particular, this will allow kpi values to repeat at different times -- and you will get one for each group of adjacent values.

Upvotes: 2

pritesh agrawal
pritesh agrawal

Reputation: 1225

You can use PARTITION BY along with ROW_NUMBER() , Below query works fine with you data : 

SELECT [Date], [Flag] FROM (
SELECT [Date], [Flag], ROW_NUMBER() OVER 
( PARTITION BY [Flag]
  ORDER BY [Date]) row_num
  FROM #test) t
WHERE t.row_num = 1

Upvotes: 0

Related Questions