Link to home
Start Free TrialLog in
Avatar of coperations07
coperations07Flag for United States of America

asked on

grouping logic

Hello,
   I'm struggling to come up with the logic/approach I need to use. The field labelfield6 contains text that should be grouped together based on the trackid field. 5 or more of the same records together is considered to be a group. For this example the last 'COM' is outside of the group and needs to be flagged. How can I go about identifying the outliers in this type of scenario?

Thanks!

User generated image
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Try using ROW_NUMBER() OVER()
select
      trackid
    , sku
    , orderid
    , labelfield6
from (
    select
          trackid
        , sku
        , orderid
        , labelfield6
        , row_number() over(partition by orderid,labelfield6 order by trackid ) as rn
    from your_table
    ) as d 
where rn > 5

Open in new window

Sorry, just re-read the question, the solution needs more. What dbms, and version of it, do you use?
I need to know if your database supports the functions LEAD() or LAG()
Hello,
What is the expected output of the input you have given?
Avatar of coperations07

ASKER

sql server 2008 r2

I need to update the records that are not in groups. a flag field (not shown) will be updated.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My apologies for the data image. I was trying to make it readable, but I understand what you are saying there. I think your solution is going to work for me. I'm still looking it over so I understand exactly what it is doing, but the results are spot on. Thanks for the help!