Solved

help with ranking

Posted on 2011-09-14
10
262 Views
Last Modified: 2012-05-12

Using SQL 2008 and 2005

Think this is where Dense rank may need to be used

There are only for rows in this sample but there may be more

Based on Email address I need to roll up the values to replace the nulls with the one with the latest entry

Rules roll up to latest email
then roll up latest values to that email

I have included 2 examples 1 in each tab..



DenseRank-sample.xlsx
0
Comment
Question by:Leo Torres
  • 6
  • 4
10 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 36538306
what you really want is the LAST_VALUE / FIRST_VALUE analytic,

but,  barring that you could try something like this...  (I've demonstrated for 2 columns, but use same technique for all)


SELECT   MAX(teamname),
         MAX(client_executive),
         emailaddrss,
         MAX(CASE WHEN mailid_rn = 1 THEN mailingid END) mailingid,
         MAX(CASE WHEN click_through_rn = 1 THEN click_through END) click_through
    FROM (SELECT t.*,
                 ROW_NUMBER()
                 OVER (PARTITION BY emailaddress
                       ORDER BY CASE WHEN mailind IS NOT NULL THEN creationtime END DESC NULLS LAST
                      )
                     mailid_rn,
                 ROW_NUMBER()
                 OVER (
                     PARTITION BY emailaddress
                     ORDER BY
                         CASE WHEN click_through IS NOT NULL THEN creationtime END DESC NULLS LAST)
                     click_through_rn
            FROM yourtable t)
GROUP BY emailaddress
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36538502

This line generates this error

ORDER BY CASE WHEN mailind IS NOT NULL THEN creationtime END DESC

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'NULL'.

And I chaged the NULLS to NULL
SELECT   MAX(teamname),
         MAX(client_executive),
         emailaddrss,
         MAX(CASE WHEN mailid_rn = 1 THEN mailingid END) mailingid,
         MAX(CASE WHEN click_through_rn = 1 THEN click_through END) click_through
    FROM (SELECT t.*,
                 ROW_NUMBER()
                 OVER (PARTITION BY emailaddress
                       ORDER BY CASE WHEN mailind IS NOT NULL THEN creationtime END DESC NULL LAST
                      )
                     mailid_rn,
                 ROW_NUMBER()
                 OVER (
                     PARTITION BY emailaddress
                     ORDER BY
                         CASE WHEN click_through IS NOT NULL THEN creationtime END DESC NULL LAST)
                     click_through_rn
            FROM ADS_CM_26.dbo.OpenClickThru t)
GROUP BY emailaddress

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36538520
you changed the syntax slightly


 NULL LAST


should be


NULLS LAST


the other NULL usage should be left as NULL
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36538555
sorry, that's oracle syntax

just remove the NULLS LAST part


ORDER BY CASE WHEN mailind IS NOT NULL THEN creationtime END DESC


ORDER BY CASE WHEN click_through IS NOT NULL THEN creationtime END DESC
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36539425
OK.

Here is a working Version..
But there are missing Columns


SELECT   MAX(teamname) as TeamName,
         MAX([client executive]) as [Client Executive],
         emailaddress,
         MAX(CASE WHEN mailid_rn = 1 THEN mailingid END) Mailingid,
         MAX(CASE WHEN click_through_rn = 1 THEN click_through END) Click_Through
    FROM (SELECT t.*,
                 ROW_NUMBER()
                 OVER (PARTITION BY emailaddress
                       ORDER BY CASE WHEN mailingid IS NOT NULL THEN creationtime END DESC
                      )
                     mailid_rn,
                 ROW_NUMBER()
                 OVER (
                     PARTITION BY emailaddress
                     ORDER BY CASE WHEN click_through IS NOT NULL THEN creationtime END DESC )
                     click_through_rn
            FROM ADS_CM_26.dbo.OpenClickThru t) a
GROUP BY emailaddress

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 73

Expert Comment

by:sdstuber
ID: 36539588
right, just do the same thing with the other columns
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36539724
in particular...

for the inner query "a", for each column xxxxxx,  you will create something like this...


 ROW_NUMBER()
                 OVER (PARTITION BY emailaddress
                       ORDER BY CASE WHEN xxxxxx   IS NOT NULL THEN creationtime END DESC
                      ) xxxxxx_rn


then, in the outer query


 MAX(CASE WHEN xxxxxx_rn = 1 THEN xxxxxx END) xxxxxx,
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36539731
you could extend it to teamname and client executive too, but from your sample data those were constant.  if they aren't do the same thing with them as you do every other column.

The only exception is emailaddress  since it's the driving column for all the others
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 36540737
Correct can't do it on email will give it a go tomorrow thanks
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 36563490
Worked thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now