By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

simple query with distinct keyword

P: n/a
Hi folks

Im trying to do a simple query involving the distinct keyword and an
access 2000 db, but have been frittering with it for amost and hour
and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
..... blue....... red........ugly
.....yeller...... red.......orange
.....green......blue.......ugly
.....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those rows with
distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my question

Issac

Sep 12 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

issac wrote:
Hi folks

Im trying to do a simple query involving the distinct keyword and an
access 2000 db, but have been frittering with it for amost and hour
and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those rows with
distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my question
Issac-

Why do you say that the row returned for ugly would be
blue..red...ugly. Maybe you want green..blue..ugly? What I'm saying is
does it matter which row is returned?
Issac
Sep 12 '06 #2

P: n/a
On 12 Sep 2006 10:41:04 -0700, "Welie" <we******@gmail.comwrote:
>
issac wrote:
>Hi folks

Im trying to do a simple query involving the distinct keyword and an
access 2000 db, but have been frittering with it for amost and hour
and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those rows with
distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my question

Issac-
>Why do you say that the row returned for ugly would be
blue..red...ugly. Maybe you want green..blue..ugly? What I'm saying is
does it matter which row is returned?
thanks for the quick response Welie

short answer is ' no'. I don't care about the row data returned as
long as the Desc Column is distinct.

Thanks for asking and clarifing, BTW. Ordinarily that would be
important, but in this case it's not. In truth, my real table columns
are named 'Team1',' Team2', & 'gameDateTime'. That table actually
contains just 2 records/rows for each unique gameDateTime. The only
difference between the 2 records is that the Teams are transposed
within the 'team' columns-- that's no biggie to my "real" select
statement. As long as I get one of them Im fine.

Since my OP, I have mucked about with 'Min(), and "group By' for
another 30 minutes so, but I still cant seem to get what Im after. Sux
being a total newb. :o(

So the answer is no, I don't care which of the 'dup' records gets
returned in my original color table example. Any one of them will do
as long as 'ugly' in the 'Desc' column is distinct.
Sep 12 '06 #3

P: n/a
I think this is what you are looking for:

strDistinct_SQL = "Select [Color1], [Color2] ,Distinct[Desc] from Colors "

Then execute the strDistinc_SQL or save it as a queryDef and execute the
query from the interface


"issac" <go****@loveboat.comwrote in message
news:h7********************************@4ax.com...
Hi folks

Im trying to do a simple query involving the distinct keyword and an
access 2000 db, but have been frittering with it for amost and hour
and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those rows with
distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my question

Issac

Sep 12 '06 #4

P: n/a
On Tue, 12 Sep 2006 17:03:26 -0400, "Kc-Mass" <co********@comcast.net>
wrote:
>"issac" <go****@loveboat.comwrote in message
news:h7********************************@4ax.com.. .
>Hi folks

Im trying to do a simple query involving the distinct keyword and an
access 2000 db, but have been frittering with it for amost and hour
and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those rows with
distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my question

Issac
>I think this is what you are looking for:

strDistinct_SQL = "Select [Color1], [Color2] ,Distinct[Desc] from Colors "

Then execute the strDistinc_SQL or save it as a queryDef and execute the
query from the interface

Thanks for the reply.

Actually, that SQL statement will not execute, syntactically
speaking. You cant designate a single column as ' distinct'. In my
OP I was actually looking for the corrected SQL statement that will
execute.
Sep 12 '06 #5

P: n/a
issac <go****@loveboat.comwrote in
news:lp********************************@4ax.com:
On 12 Sep 2006 10:41:04 -0700, "Welie" <we******@gmail.com>
wrote:
>>
issac wrote:
>>Hi folks

Im trying to do a simple query involving the distinct
keyword and an access 2000 db, but have been frittering with
it for amost and hour and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but
it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those
rows with distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my
question

Issac-

>>Why do you say that the row returned for ugly would be
blue..red...ugly. Maybe you want green..blue..ugly? What I'm
saying is does it matter which row is returned?

thanks for the quick response Welie

short answer is ' no'. I don't care about the row data
returned as long as the Desc Column is distinct.

Thanks for asking and clarifing, BTW. Ordinarily that would be
important, but in this case it's not. In truth, my real table
columns are named 'Team1',' Team2', & 'gameDateTime'. That
table actually contains just 2 records/rows for each unique
gameDateTime. The only difference between the 2 records is
that the Teams are transposed within the 'team' columns--
that's no biggie to my "real" select statement. As long as I
get one of them Im fine.

Since my OP, I have mucked about with 'Min(), and "group By'
for another 30 minutes so, but I still cant seem to get what
Im after. Sux being a total newb. :o(

So the answer is no, I don't care which of the 'dup' records
gets returned in my original color table example. Any one of
them will do as long as 'ugly' in the 'Desc' column is
distinct.
What you want is
SELECT first([Color 1]) as One, first([Color 2]) as Two, Desc
from Table group by Desc ;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Sep 13 '06 #6

P: n/a
On 12 Sep 2006 23:34:37 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:
>issac <go****@loveboat.comwrote in
news:lp********************************@4ax.com :
>On 12 Sep 2006 10:41:04 -0700, "Welie" <we******@gmail.com>
wrote:
>>>
issac wrote:
Hi folks

Im trying to do a simple query involving the distinct
keyword and an access 2000 db, but have been frittering with
it for amost and hour and a half and I cant make it work.

This is the SQL I would run if it were valid syntax, but
it's not:
Select COLOR1, COLOR2, distinct DESC from COLORS;

TABLE COLORS
---------------------------------------
| COLOR1 | COLOR2 | DESC |
---------------------------------------
.... blue....... red........ugly
....yeller...... red.......orange
....green......blue.......ugly
....red........green......ugly
---------------------------------------

I want to select all columns in the table, but only those
rows with distinct values in the column 'Desc'.
so my returned row countfrom that table would be 2:
.... blue....... red........ugly
....yeller...... red........orange
I am a bit of a newb to SQL, so pardon the 'basicness' of my
question
Issac-

>>>Why do you say that the row returned for ugly would be
blue..red...ugly. Maybe you want green..blue..ugly? What I'm
saying is does it matter which row is returned?

thanks for the quick response Welie

short answer is ' no'. I don't care about the row data
returned as long as the Desc Column is distinct.

Thanks for asking and clarifing, BTW. Ordinarily that would be
important, but in this case it's not. In truth, my real table
columns are named 'Team1',' Team2', & 'gameDateTime'. That
table actually contains just 2 records/rows for each unique
gameDateTime. The only difference between the 2 records is
that the Teams are transposed within the 'team' columns--
that's no biggie to my "real" select statement. As long as I
get one of them Im fine.

Since my OP, I have mucked about with 'Min(), and "group By'
for another 30 minutes so, but I still cant seem to get what
Im after. Sux being a total newb. :o(

So the answer is no, I don't care which of the 'dup' records
gets returned in my original color table example. Any one of
them will do as long as 'ugly' in the 'Desc' column is
distinct.

What you want is
SELECT first([Color 1]) as One, first([Color 2]) as Two, Desc
from Table group by Desc ;

Thanks Bob. Your solution works a treat.

I had actually tried both the first() and min() functions on the Desc
column, but not on the color columns while I was trying to work out
the solution.

I still dont understand why 'first()' functions the way it does
though. Using common sense, I would think that using ' first([Desc])'
would be the way to go and that would simply take the first dupped
record available in the Desc column and ignore the rest of them. So
Im at a loss to understand why 'first()' on all the other columns is
the correct solution --- but it works fine.

thanks again.

Issac
Sep 13 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.