472,954 Members | 1,620 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,954 software developers and data experts.

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 16875
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the...
2
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an...
9
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my...
18
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are...
1
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT...
5
by: Fred Zuckerman | last post by:
Can someone explain the difference between these 2 queries? "Select Distinct id, account, lastname, firstname from table1" and "Select DistinctRow id, account, lastname, firstname from table1" ...
1
by: Patrick.O.Ige | last post by:
I have a xml file and i want to format it using XSL My XSL file and XML below I needed to do a distinct which is ok on the first node "Code" For the "programDescription" i did below which gets the...
6
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214,...
5
by: Daniel Wetzler | last post by:
Dear MSSQL experts, I use MSSQL 2000 and encountered a strange problem wqhile I tried to use a select into statement . If I perform the command command below I get only one dataset which has...
1
by: Bill | last post by:
I'm trying to write a query that will select a distinct count of more than one field. I have records that display user productivity. Each of the records have a time associated with it and I want to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
1
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.