Re: simple query with distinct keyword
- From: issac <gopher@xxxxxxxxxxxx>
- Date: Wed, 13 Sep 2006 03:53:09 GMT
On 12 Sep 2006 23:34:37 GMT, Bob Quintal <rquintal@xxxxxxxxxxxxx>
wrote:
issac <gopher@xxxxxxxxxxxx> wrote inThanks Bob. Your solution works a treat.
news:lpudg29kqe7vv3e9e48uts17abjb1uge46@xxxxxxx:
On 12 Sep 2006 10:41:04 -0700, "Welie" <welyakim@xxxxxxxxx>
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 ;
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
.
- References:
- simple query with distinct keyword
- From: issac
- Re: simple query with distinct keyword
- From: Welie
- Re: simple query with distinct keyword
- From: issac
- Re: simple query with distinct keyword
- From: Bob Quintal
- simple query with distinct keyword
- Prev by Date: Re: size of database Access2000...plz...
- Next by Date: Compact And Repair Menu Option MISSING
- Previous by thread: Re: simple query with distinct keyword
- Next by thread: Re: simple query with distinct keyword
- Index(es):
Relevant Pages
|
