Distinct and Top in Select

M

mianiro

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?
 
B

Bob Barrows [MVP]

mianiro said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top