Solved

help with ranking

Posted on 2011-09-14
10
287 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 74

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 74

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 74

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
 
LVL 74

Expert Comment

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

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 74

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 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