Distinct and Top in Select

**Bob Barrows [MVP]** · Jan 2 '07, 03:45 PM

Re: Distinct and Top in Select

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.

Distinct and Top in Select

Distinct and Top in Select

Comment