473,322 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

simple query with distinct keyword

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
6 4312

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

Similar topics

4
by: Robert | last post by:
Greetings I am assisting a developer with an Access application performance problem and an ODBC timeout. In a nutshell they have a combo box with a drop down that queries a lookup table on a SQL...
7
by: Riley DeWiley | last post by:
I am continually amazed by SQL's ability to humble me .... I have a toy query into a toy database that looks just like this: SELECT . FROM f, fw, w WHERE f.id = fw.fid and fw.wid = w.id and...
1
by: Robert | last post by:
I am trying to create a db for service providers by county. I'm relatively new to db programming, but I have done quite a bit of programming ranging from the old basic days up to doing some...
4
by: Michael | last post by:
Hi, If I have R(a integer PRIMARY KEY, b text, c text, d integer); and I want to find how many different entries there are, (specified using b and c instead of a), is "select count(distinct b||c)...
2
by: BerkshireGuy | last post by:
I have the following union query that works great, but I want to alter the query to only return True (-1) for the TEST column. Currently the test column DOES return 0 or -1. I just want the -1....
5
by: Nobody | last post by:
I'm trying to write a stored proc... Basically, I have a tblItems table which contains a list of every item available. One of the columns in this table is the brand... for test purposes, I...
4
by: Dia | last post by:
Hi there, I struggle to get this going i would like to insert data into 2 tmp tables in a view. If i run the code on it's own it works perfectly until i want to create a view it complains...
23
by: Bosnoval | last post by:
Access 2003 Databse ASP.NET 2 Win 2003 Server Currently I have a search page that only allows a single keyword (person) to be searched at a time. I thought upgrading that option via a multiple...
7
by: nofear | last post by:
I have a table with 2 fields where one has duplicates and the other does not something like this: ID field1 field2 1 A A 2 A B 3 A C...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.