Distinct and Top in Select

Discussion in 'ASP General' started by mianiro, Jan 2, 2007.

  1. mianiro

    mianiro Guest

    I want to use a distinct and a top in a select statement, however I
    want the distinct to only apply to one of the columns. For example

    SELECT DISTINCT TOP 15 col1,col2,col3,col4 from table1

    I only want the distinct to apply to say col2. Is there a way to do
    this?
    mianiro, Jan 2, 2007
    #1
    1. Advertising

  2. mianiro wrote:
    > I want to use a distinct and a top in a select statement, however I
    > want the distinct to only apply to one of the columns. For example
    >
    > SELECT DISTINCT TOP 15 col1,col2,col3,col4 from table1
    >
    > I only want the distinct to apply to say col2. Is there a way to do
    > this?


    You have to use GROUP BY on col2 and appropriate aggregate functions on
    the other columns. Here is my canned reply about group by:

    Say you have a table with two columns containing the following 4 rows of
    data:

    Col1 Col2
    1 28
    1 33
    2 5
    2 8

    Now you decide to create a grouping query:
    select Col1 From table Group By Col1
    You would get these results:
    1
    2

    Now you decide to add Col2:
    select Col1,Col2 From table Group By Col1
    Here are the results:
    1 ?
    2 ?

    What do you replace the ?'s with? Do you see the problem?

    Bottom line: You have to tell the query engine how to aggregate Col2
    (min,
    max,sum, avg,count, etc.) so that you wind up with a single row for each
    value in Col1.

    Even if Col2 contains the value 5 in every row, you still have to tell
    the
    engine how to aggregate it. The query parser does not know what's in
    your
    table: all it can see is that there is a column in the select list that
    needs aggregation.



    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Jan 2, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?YmVub2l0?=

    Select Distinct DataGrid

    =?Utf-8?B?YmVub2l0?=, Oct 14, 2005, in forum: ASP .Net
    Replies:
    4
    Views:
    2,116
    =?Utf-8?B?YmVub2l0?=
    Oct 14, 2005
  2. will
    Replies:
    1
    Views:
    1,131
    Pavel Lepin
    Aug 15, 2007
  3. Hicham Mouline
    Replies:
    1
    Views:
    379
    Kai-Uwe Bux
    Apr 11, 2010
  4. andrewmrichards

    XPATH equivalent of SELECT DISTINCT

    andrewmrichards, Dec 2, 2010, in forum: XML
    Replies:
    2
    Views:
    3,389
    Martin Honnen
    Dec 3, 2010
  5. JZ
    Replies:
    0
    Views:
    118
Loading...

Share This Page