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 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.