SELECT options

  • Comments posted to this topic are about the item SELECT options

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Very very nice question (not only because my name is in it :-D)

    Never used WITH TIES before, so I definately learned something.

    Good question to close the week.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I am getting the below. Please correct me.:cool:

  • I am getting the below. Please correct me.:cool:

  • Nice question....

    -----------------
    Gobikannan

  • masteratul25 (4/12/2012)


    I am getting the below. Please correct me.:cool:

    I get an error when I try to open your attachment.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good Question. Made me think a lot.

    Though I knew the existence of WITH TIES option, I never really used it and never bothered to get the exact details of how it works.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Definitely had to earn my point today - took a fair bit of thought.

    Good question, thank you

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Interesting question, and I learned something new from it.

    Cheers!

  • Nice week closing question.

    I've never used WITH TIES so learned something new yet again.

    Have a great weekend folks.

    😀

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • Good question about some little known features (PERCENT and WITH TIES) of the TOP clause.

    Two minor quibbles:

    1. It would have been better to present the data ordered by salary. I first tried to order them by head, but I was constantly afraid that I'd overlook something. I ended up creating a table, putting in the salary values from the question, and running a SELECT * FROM ThatTable ORDER BY value DESC, just so that I could see the values in order to work out the correct example.

    2. The syntax used in this question is deprecated; Microsoft recommends always using parentheses with the TOP clause: TOP (5), TOP (5) WITH TIES, TOP (80) PERCENT, and TOP (80) PERCENT WITH TIES.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I am confused.

    How come this is supposed to bring back 6 rows?

    select top 5 with ties * from SSCSales order by cMonthSales desc

    There are 4 records with 3500. So those will return. The fifth element would be 4200 for which there are 3.

    As with ties will bring through all of the 4200`s i make that 7?

    What am i doing wrong?

    Dan

  • danielfountain (4/13/2012)


    I am confused.

    How come this is supposed to bring back 6 rows?

    select top 5 with ties * from SSCSales order by cMonthSales desc

    There are 4 records with 3500. So those will return. The fifth element would be 4200 for which there are 3.

    As with ties will bring through all of the 4200`s i make that 7?

    What am i doing wrong?

    Dan

    The sort is descending, so you need to start with the highest numbers.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • danielfountain (4/13/2012)


    I am confused.

    How come this is supposed to bring back 6 rows?

    select top 5 with ties * from SSCSales order by cMonthSales desc

    There are 4 records with 3500. So those will return. The fifth element would be 4200 for which there are 3.

    As with ties will bring through all of the 4200`s i make that 7?

    What am i doing wrong?

    Dan

    Are you overlooking the "desc" option in the order by clause?

    Since sales are sorted in descending order, the top 5 are 6100, 5700, 5400, and two of the three 4200's. The WITH TIES adds the third 4200.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (4/13/2012)


    danielfountain (4/13/2012)


    I am confused.

    How come this is supposed to bring back 6 rows?

    select top 5 with ties * from SSCSales order by cMonthSales desc

    There are 4 records with 3500. So those will return. The fifth element would be 4200 for which there are 3.

    As with ties will bring through all of the 4200`s i make that 7?

    What am i doing wrong?

    Dan

    Are you overlooking the "desc" option in the order by clause?

    Since sales are sorted in descending order, the top 5 are 6100, 5700, 5400, and two of the three 4200's. The WITH TIES adds the third 4200.

    Doh!!! I thought i was missing something obvious as no one else had questioned it. Long week!

Viewing 15 posts - 1 through 15 (of 61 total)

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