locked
How to include "Top 100" into query? RRS feed

  • Question

  • Hi All,

    I have query something like this:-

    Select Distinct ID, Name,.......etc more number of columns
              From TableName

    Now I want to include Top 100 into the query, which can give me FIRST 100 records, like this:-

    Select Top 100 Distinct ID, Name,.......etc more number of columns
              From TableName

    But above query is giving me errors "Incorrect syntax near the keyword 'DISTINCT'"

    Can anybody help me out?

    Thanks


    regards,
    Kumar
    Thursday, December 3, 2009 4:47 PM

Answers

  • DISTINCT goes first

    Select Distinct Top 100  ID, Name,.......etc more number of columns
              From TableName


    Abdallah, PMP, ITIL, MCTS
    • Proposed as answer by Phil Brammer Thursday, December 3, 2009 5:01 PM
    • Marked as answer by gk1393 Thursday, December 3, 2009 5:22 PM
    Thursday, December 3, 2009 4:50 PM
  • The DISTINCT keyword has to be between SELECT and TOP:

    SELECT DISTINCT TOP 100 ...
    Plamen Ratchev
    • Proposed as answer by Phil Brammer Thursday, December 3, 2009 5:01 PM
    • Marked as answer by Phil Brammer Thursday, December 3, 2009 5:23 PM
    Thursday, December 3, 2009 4:50 PM

All replies

  • DISTINCT goes first

    Select Distinct Top 100  ID, Name,.......etc more number of columns
              From TableName


    Abdallah, PMP, ITIL, MCTS
    • Proposed as answer by Phil Brammer Thursday, December 3, 2009 5:01 PM
    • Marked as answer by gk1393 Thursday, December 3, 2009 5:22 PM
    Thursday, December 3, 2009 4:50 PM
  • The DISTINCT keyword has to be between SELECT and TOP:

    SELECT DISTINCT TOP 100 ...
    Plamen Ratchev
    • Proposed as answer by Phil Brammer Thursday, December 3, 2009 5:01 PM
    • Marked as answer by Phil Brammer Thursday, December 3, 2009 5:23 PM
    Thursday, December 3, 2009 4:50 PM
  • Hi Abdallah,

    I ran the query like this:-

    Select Top 100 a.ID, a.Name, .....etc more number of columns
              From
                     (Select Distinct Top 100  ID, Name,.......etc more number of columns
                            From TableName) a

    Does my query gives same result as yours query reply by using "DISTINCT goes first"

    Thanks


    regards,
    Kumar
              

    Thursday, December 3, 2009 4:59 PM
  • Yeah, but why would you do that? Why would you include the derived table?

    Abdallah, PMP, ITIL, MCTS
    Thursday, December 3, 2009 5:16 PM
  • This was because before your reply I ran the query using my logic so just was curious to know before I apply your logic that my logic and your logic will produce same result but obviously Derived Table will be a problem.

    So with this reply I'm closing this thread by marking your 1st answer as ANSWERED.

    thanks anyways


    regards,
    Kumar
    Thursday, December 3, 2009 5:21 PM
  • Kumar, one moer thing. The TOP is nondeterministic if ORDER BY is not included. This means that the first time you run it and you get TOP 100 for example, and you run it the second time, there is not guarantee that you will get the same 100 results. Keep that in mind because if you want the TOP 100 based on some data in your table, include the ORDER BY clause.

    Abdallah, PMP, ITIL, MCTS
    Thursday, December 3, 2009 5:25 PM
  • Using TOP will give you unreliable results UNLESS you also add an ORDER BY DESC for the top 100, and ORDER BY ASC for the bottom 100..
    "You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson
    Thursday, December 3, 2009 5:26 PM