Leo Torres
asked on
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
ASKER
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
you changed the syntax slightly
NULL LAST
should be
NULLS LAST
the other NULL usage should be left as NULL
NULL LAST
should be
NULLS LAST
the other NULL usage should be left as NULL
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
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
ASKER
OK.
Here is a working Version..
But there are missing Columns
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
right, just do the same thing with the other columns
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
The only exception is emailaddress since it's the driving column for all the others
ASKER
Correct can't do it on email will give it a go tomorrow thanks
ASKER
Worked thanks
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