470,636 Members | 1,526 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,636 developers. It's quick & easy.

Distinct and Top in Select

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?

Jan 2 '07 #1
1 16778
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.
Jan 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Martin Feuersteiner | last post: by
2 posts views Thread by mfyahya | last post: by
9 posts views Thread by Kelvin | last post: by
5 posts views Thread by Fred Zuckerman | last post: by
5 posts views Thread by Daniel Wetzler | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.