SELECT DISTINCT

  • Thread starter Thread starter Nathan
  • Start date Start date
N

Nathan

Hi,

I'm using an oledb adapter to connect to Access. I need to select three
columns from a database and load them to a dataset: Name, Number, and
Category. Name and Number are unique for each record, but many records can
have the same Category. But I want to do a SELECT DISTINCT for only the
Name, but get the Number and Category as well. I first had only the first
two columns, and this worked fine:

SELECT DISTINCT Name, Number FROM DatabaseTable ORDER BY Name

This works because the Name and Number are unique for each record. But now
I have added Category to the table; how do I add it to the selection query?

SELECT DISTINCT Name, Number, Category FROM DatabaseTable ORDER BY Name

brings an error.

Thanks for your help.

Nathan
 
First off, you have to tell us the error if you want someone to be able to
tell you what's wrong.

Second of all, if you are selecting all 3 columns, and the first 2 are
always unique, then the combination of the 3 records will always be unique
anyway. So there is no point in selecting distinct, is there?
 
Nathan:

In this query, Department and Transmission_Proc are always unique, however
code is repeated...this is the same scenario you have and it works fine.
SELECT DistincT(Department), Transmission_SP, Code from
Tbl_Transmission_Procs
The likely culprit of your problem is the use of the word NAME for a field.
Try
SELECT DISTINCT [Name], Number FROM DatabaseTable ORDER BY [Name]

Or (The better approach)
SELECT DISTINCT _Name, Number FROM DatabaseTable ORDER BY _Name
It's a reserved word and the most likely cause of your troubles
http://www.knowdotnet.com/articles/reservedwords.html
Using Reserved Words for Field Names isn't worth it..even if it takes some
rework, this problem will show itself again and again b/c someone is going
to forget it's reservered
 
Thanks for the code sample. I've changed my database column names to avoid
using keywords, and it works fine now.


William Ryan said:
Nathan:

In this query, Department and Transmission_Proc are always unique, however
code is repeated...this is the same scenario you have and it works fine.
SELECT DistincT(Department), Transmission_SP, Code from
Tbl_Transmission_Procs
The likely culprit of your problem is the use of the word NAME for a field.
Try
SELECT DISTINCT [Name], Number FROM DatabaseTable ORDER BY [Name]

Or (The better approach)
SELECT DISTINCT _Name, Number FROM DatabaseTable ORDER BY _Name
It's a reserved word and the most likely cause of your troubles
http://www.knowdotnet.com/articles/reservedwords.html
Using Reserved Words for Field Names isn't worth it..even if it takes some
rework, this problem will show itself again and again b/c someone is going
to forget it's reservered
Nathan said:
Hi,

I'm using an oledb adapter to connect to Access. I need to select three
columns from a database and load them to a dataset: Name, Number, and
Category. Name and Number are unique for each record, but many records can
have the same Category. But I want to do a SELECT DISTINCT for only the
Name, but get the Number and Category as well. I first had only the first
two columns, and this worked fine:

SELECT DISTINCT Name, Number FROM DatabaseTable ORDER BY Name

This works because the Name and Number are unique for each record. But now
I have added Category to the table; how do I add it to the selection query?

SELECT DISTINCT Name, Number, Category FROM DatabaseTable ORDER BY Name

brings an error.

Thanks for your help.

Nathan
Click to expand...
Click to expand...
 
Back
Top