Post reply

Custom Ranking?

  • August 21, 2013 at 9:27 pm

    #277185

    Hello everyone.

    This is my first post. I think this a wonderful resource and I hope to answer other people's questions myself one day.

    I have a simple question, i guess. I want to rank a set of records but I need to use a custom rank, rather than by alphabetical or numeric sort

    I have a table, for example:

    IDViolation

    ----------------

    ID1Warning

    ID21st Citation

    ID22nd Citation

    ID23rd Citation

    ID3Warning

    ID4Warning

    ID51st Citation

    ID62nd Citation

    I want my result set to be a count of ID's grouped by severity, but only by max severity.

    In other words, the results for this should be:

    Warning3

    1st Citation1

    2nd Citation1

    3rd Citation1

    Maybe I'm sleepy but every solution I've come up with keeps counting those citations for ID2.

    Does anyone have any insight on this?

    Thanks in advance

  • August 21, 2013 at 10:28 pm

    #1643740

    WElcome to the SSC 🙂

    can you please provide some information about your requirement? Its not clear to us..

    Please follow the link in my signature about how to post a question, you will get more responses if you post question in that manner 😎

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 21, 2013 at 11:26 pm

    #1643752

    mpatterson3 (8/21/2013)

    Hello everyone.

    This is my first post. I think this a wonderful resource and I hope to answer other people's questions myself one day.

    I have a simple question, i guess. I want to rank a set of records but I need to use a custom rank, rather than by alphabetical or numeric sort

    I have a table, for example:

    IDViolation

    ----------------

    ID1Warning

    ID21st Citation

    ID22nd Citation

    ID23rd Citation

    ID3Warning

    ID4Warning

    ID51st Citation

    ID62nd Citation

    I want my result set to be a count of ID's grouped by severity, but only by max severity.

    In other words, the results for this should be:

    Warning3

    1st Citation1

    2nd Citation1

    3rd Citation1

    Maybe I'm sleepy but every solution I've come up with keeps counting those citations for ID2.

    Does anyone have any insight on this?

    Thanks in advance

    You'd need to enumerate the various violations in order by the desired rank. Once that's done, you'd run ROW_NUMBER() OVER partitioning by ID and ordering by violation rank in descending order to determine the max violation for each ID. Once that's done, do a count aggregate with a GROUP BY the ID where the ROW_NUMBER = 1.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 21, 2013 at 11:33 pm

    #1643754

    Thank you for the welcome! I have read the post you provided and I will format all further questions in the appropriate manner. Thank you for responding and for your help.

  • August 21, 2013 at 11:41 pm

    #1643757

    Jeff Moden (8/21/2013)

    mpatterson3 (8/21/2013)

    Hello everyone.

    This is my first post. I think this a wonderful resource and I hope to answer other people's questions myself one day.

    I have a simple question, i guess. I want to rank a set of records but I need to use a custom rank, rather than by alphabetical or numeric sort

    I have a table, for example:

    IDViolation

    ----------------

    ID1Warning

    ID21st Citation

    ID22nd Citation

    ID23rd Citation

    ID3Warning

    ID4Warning

    ID51st Citation

    ID62nd Citation

    I want my result set to be a count of ID's grouped by severity, but only by max severity.

    In other words, the results for this should be:

    Warning3

    1st Citation1

    2nd Citation1

    3rd Citation1

    Maybe I'm sleepy but every solution I've come up with keeps counting those citations for ID2.

    Does anyone have any insight on this?

    Thanks in advance

    You'd need to enumerate the various violations in order by the desired rank. Once that's done, you'd run ROW_NUMBER() OVER partitioning by ID and ordering by violation rank in descending order to determine the max violation for each ID. Once that's done, do a count aggregate with a GROUP BY the ID where the ROW_NUMBER = 1.

    I did come up with a solution finally. It involved a lot of nested subqueries! I did all the steps you listed but in reverse order and using case statements instead of row_number. I would much rather use your solution.

    Thanks alot. I appreciate it. I never expected to get help this fast.

  • August 22, 2013 at 12:00 am

    #1643759

    Not sure why you need to use subqueries. I'd do it with a ranking table.

    WITH Ranking (n, Citation) AS (

    SELECT 1,'Warning'

    UNION ALL SELECT 2,'1st Citation'

    UNION ALL SELECT 3, '2nd Citation'

    UNION ALL SELECT 4, '3rd Citation')

    ,Citations (ID, Violation) AS (

    SELECT 'ID1','Warning'

    UNION ALL SELECT 'ID2','1st Citation'

    UNION ALL SELECT 'ID2','2nd Citation'

    UNION ALL SELECT 'ID2','3rd Citation'

    UNION ALL SELECT 'ID3','Warning'

    UNION ALL SELECT 'ID4','Warning'

    UNION ALL SELECT 'ID5','1st Citation'

    UNION ALL SELECT 'ID6','2nd Citation'

    )

    SELECT Violation, Number=COUNT(*)

    FROM Citations a

    INNER JOIN Ranking b ON b.Citation = a.Violation

    GROUP BY Violation

    ORDER BY MIN(b.n)

    Assuming I understand the requirement correctly of course.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply