• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

help with ranking


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
Leo Torres
Asked:
Leo Torres
  • 6
  • 4
1 Solution
 
sdstuberCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:

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
 
sdstuberCommented:
you changed the syntax slightly


 NULL LAST


should be


NULLS LAST


the other NULL usage should be left as NULL
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sdstuberCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
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
 
sdstuberCommented:
right, just do the same thing with the other columns
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
Leo TorresSQL DeveloperAuthor Commented:
Correct can't do it on email will give it a go tomorrow thanks
0
 
Leo TorresSQL DeveloperAuthor Commented:
Worked thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now