Select Distinct and Order By something else.

[hooty]

Hello my friends
I need some help with a query.

I’ve got a table and like to select distinct coll prod_number and order my output by coll id autonumber.

I know it won’t work with a basic query such as



SELECT DISTINCT prod_number FROM mytable ORDER BY id DESC

But is there a (simple) solution for this.

Someone?

Thx...

[JoeM]

So, it sounds like you have multiple prod_number records (duplicates), but with different Autonumbers and you want to collapse them down to a single record.
So, what is the logic for which Autonumber record you want to return/sort on? The highest one? The lowest one?

[hooty]

hi,
the last record added should always come up first when displaying the table records.




Using "SELECT DISTINCT (PRODUCT_CODE_LONG) FROM (TMP_ORDERS) ORDER BY PRODUCT_CODE_LONG DESC"

Record 8 (Asc) or 10 (Desc) came up first. But i want record 12 should came up firts.

Thx...

[JoeM]

Try using an Aggregate Query instead, like this:

SELECT
    PRODUCT_CODE_LONG,
    MAX(ID)
FROM
    TMP_ORDERS
GROUP BY
    PRODUCT_CODE_LONG
ORDER BY
    2 DESC;

[hooty]

Thank You, Thank You,Thank You, Thank You,Thank You, Thank You,Thank You, Thank You and again Thank You!

This will do the job perfectly....

[JoeM]

Glad to help!

If you get the chance, you may want to look into Aggregate Queries a bit more. They can be built using the Query Builder (without having to know VBA).
They are very useful when trying to group records by certain fields, which performing some calculation on that group (like Count, Sum, Avg, Min, Max, etc).

[hooty]

oh okay...
I will definitely do that.
thanks again