DISTINCT Problem

[BlueBeep]

Here's my query. It's for an access db - I'm trying to return both columns, the product id and productdate, order it by productdate, and have no duplicate records based on productid.

Is this possible? I'm was using distinct before, without luck. I removed it, but nothing still. Here's the query I'm trying to use without luck. Thanks.

SELECT ProductID, ProductDate
FROM Products
WHERE ProductDate >= #1/1/03# AND ProductDate <= #6/1/03#
GROUP BY ProductID, ProductDate
ORDER BY ProductDate;

I'm still getting some duplicate ProductIDs. (Duplicate ProductsIDs when the ProductDate is different). Not as much as before. Any thoughts? I gladly appreciate it. Thanks!

 

[sleipnir214]

Is the MySQL database server involved in this at all?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BlueBeep]

I tried this in MySQL without luck either.

 

[abombss]

Well read up on SQL

http://www.w3schools.com/sql/default.asp

The

DISTINCT

clause only eliminates duplicate rows from the result, not duplicate columns. If you want unique product id's order by date, but don't need the date try this.

SELECT DISTINCT ProductID, ProductDate 
FROM Products 
WHERE ProductDate >= #1/1/03# AND ProductDate <= #6/1/03#
ORDER BY ProductDate;

If you need the date and need unique product ids then you need to decide what date do you want. Do you want the greatest,

MAX

, or the least,

MIN

. Then you can do this.

SELECT ProductID, MAX(ProductDate) 
FROM Products 
WHERE ProductDate >= #1/1/03# AND ProductDate <= #6/1/03# 
GROUP BY ProductID
ORDER BY ProductDate;

You only need a

GROUP BY

when you are using an aggregate function:

MAX

,

MIN

,

SUM

,

AVG

,

COUNT

, etc.

 

[abombss]

Sorry... I submitted the previous post on accident. Here is the correct syntax for the first query I posted.

SELECT DISTINCT ProductID
FROM Products 
WHERE ProductDate >= #1/1/03# AND ProductDate <= #6/1/03#
ORDER BY ProductDate;

Good Luck,

abombss

 

[r937]

abombss, some databases will not allow you to sort a result set by a column not included in the result set

your &quot;correct syntax&quot; might actually run in mysql but it doesn't make sense



also, your comment &quot;You only need a GROUP BY when you are using an aggregate function: MAX, MIN, SUM, AVG, COUNT, etc.&quot; is not quite right, or at least needs some qualification

if you use an aggregate function, then you must use GROUP BY on any non-aggregates in the SELECT list, but if there are no non-aggregates in the SELECT list, then the entire result set is treated as one large group

for example, [tt]select count(*) from addresses[/tt]

further, if you do have GROUP BY, you do not necessarily need an aggregate function

for example, [tt]select prov from addresses group by prov[/tt]

this last example works the same way and gives the same result as [tt]select distinct prov from addresses[/tt]

rudy

http://r937.com/