?
Solved

help with ranking

Posted on 2011-09-14
10
Medium Priority
?
301 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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
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

Technology Partners: 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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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