Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

help with ranking

Posted on 2011-09-14
10
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

604 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