"I would like to then display all of the fields within the DB, not just the prod_name field"

there are two ways to interpret your question

first, you could be after only one row from each prod_name, in which case, since you have multiple rows with the same prod_name field, such that you have to use DISTINCT on it, how are you going to decide which one of the multiple rows for each value of prod_name you want? the one with the lowest alphabetical description? color?

the other way to interpret your question is that you want "styled" output, in other words, you want all rows returned, you just don't want to print the prod_name more than once

something like

prod_name prod_desc prod_color
abc abc in black black
abc in white white
xyz xyz in red red

if this is what you want, you need to do this with a reporting program

rudy
http://rudy.ca/

I think what he was asking was this:
I have the following table called Orders (i've numbered them to help the explaination)

Ordnum Name City
1 11111 Nick NYC
2 11111 Nick ATL
3 22222 Joe CHI
4 33333 Joe CHI

Now I want to select records based on the uniqueness of Ordnum only

SELECT DISTINCT Ordnum from Orders;

Lets say it returned records 1, 3, and 4. The following would be displayed:

Ordnum
1 11111
3 22222
4 33333

But this is what I want to display:

Ordnum Name City
1 11111 Nick NYC
3 22222 Joe CHI
4 33333 Joe CHI

So in short I want to display all of the fields but only have the DISTINCT statement apply to the Ordnum field, I don't care if Name and City are distinct or not.

I'm thinking it should be something like the following but I can't figure out a way to make it work:
SELECT *
FROM Order
WHERE Ordnum
IN (SELECT DISTINCT Ordnum
FROM Orders;);

Any help would be greatly appreciated...

Thanks

that's a good description, except there's a tiny problem with your logic
SELECT DISTINCT Ordnum from Orders;

Lets say it returned records 1, 3, and 4. The following would be displayed:

Ordnum
1 11111
3 22222
4 33333 the problem is that SELECT DISTINCT does not display rows from the original table, it displays values, and it returns these values as rows in a "new" table, the result set of the query

so it would be better to say it returned

47 11111
48 22222
49 33333

where the "new" row numbers are different from the original table

now, when you want to select rows from the original table, you can match them against this "new" table...

... but the big question is (and has always been) how do you exclude one of the 11111's and pick the other one? i mean, what determines which one you pick? because if you don't do anything about it, both values of 11111 in the original table will match the 11111 in the "new" table

obviously, the answer is, you have to supply some other criterion

like, for example, pick the row that has 11111 and also has the highest city name (in this case, NYC)

once you specify that extra condition, it is easy to make a connection to the "new" table of distinct valuesselect Ordnum, Name, City,
from Orders XX
where City =
( select max(City)
from Orders
where Ordnum = XX.Ordnum )in this example, we use a correlated subquery to select the highest City name of all the rows that have the same Ordnum as the row being considered

there is a way to do it with a join too, but i prefer the correlated subquery because it's very clear what it's doing

rudy

wonderful, looks perfect...i was having trouble figuring out how to make the sub-query return 1 result...but still give me the columns i needed...never thought to use a fucntion to do this...hehe...

I had considered the join method also but I was having trouble joining the same table together, and getting the results that I needed...I am assuming that the same method of using a fuction to return a single result would be the secret to making that work also?

I'll test it out and let you know how I get along

This doesn't apply to this situation since the query is for an Access database but here it is anyway.

DISTINCT ON would allow me to select multiple fields but only have to distinct criteria apply to some of the fields correct?

Thanks again!

no, be careful with DISTINCT -- it is used to detect duplicate rows

for example,

SELECT DISTINCT X FROM ...

if you select only one column, then the result set is only one column, in effect you are getting distinct values of X

SELECT DISTINCT X, Y FROM ...

if you select two columns, then the result set is two columns, and the DISTINCT operator guarantees distinct combination of values of X and Y

i.e. distinct rows

the most common sql error i've seen with regard to DISTINCT is assuming that it applies only to the first column in the list,

SELECT DISTINCT X,
A, B, C, D... FROM ...

which, of course, will be distinct for all combinations of X, A, B, C, D...

DISTINCT requires a sort

just prior to being returned to you by the database, the result set is sorted on all columns -- this enables duplicate rows to be detected and elimininated



rudy