SELECT with DISTINCT and TOP

• Thomas Franz

  Hall of Fame

  Points: 3930

  More actions

  June 17, 2017 at 11:28 pm

  #331176

  Comments posted to this topic are about the item SELECT with DISTINCT and TOP

  God is real, unless declared integer.

• George Vobr

  SSChampion

  Points: 10083

  More actions

  June 19, 2017 at 4:42 am

  #1947225

  Thanks Thomas for this question. Yes, back to the basics, but I still looked into MSDN before answering ...

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  June 19, 2017 at 4:57 am

  #1947227

  Nice question. Thanks.

  ---

  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/

• sknox

  SSChampion

  Points: 12388

  More actions

  June 19, 2017 at 5:55 am

  #1947232

  I always get the order of these two confused, because it's one of the places where SQL diverges from English.
  In English, DISTINCT TOP 5 would mean "get the distinct values of the top five results" -- which with this data set would return only one row (1), whereas TOP 5 DISTINCT clearly means "get the top 5 values of the distinct results."
  SQL uses the first syntax, but to mean the second instruction.

• webrunner

  SSC-Dedicated

  Points: 32000

  More actions

  June 19, 2017 at 9:14 am

  #1947270

  Thanks for this instructive question.

  -------------------
  A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
  Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

• Revenant

  SSC-Forever

  Points: 42467

  More actions

  June 19, 2017 at 10:25 am

  #1947282

  Nice - thanks, Thomas!

• Stewart "Arturius" Campbell

  SSC Guru

  Points: 72727

  More actions

  June 20, 2017 at 2:04 am

  #1947343

  Nice, simple question, thanks Thomas

  ____________________________________________
  Space, the final frontier? not any more...
  All limits henceforth are self-imposed.
  “libera tute vulgaris ex”