Solved How to Sort a Distinct Field (1 Viewer)

[Sun_Force]

I'm trying to fill a listbox with a list of mm/dd formatted dates.
This is my try :

SELECT DISTINCT Format([Delivery],"mm/dd") AS AD
FROM tblOrders WHERE Delivery Is Not Null AND Deleted=False
ORDER BY Delivery ASC;

I'm receiving this error:
ORDER BY clause (Delivery) conflicts with DISTINCT

How can I sort this query.

Note: I will built the sql and use something like:
MyListBox.RowSource=sql

Thanks for your advice.[/code]

 

[theDBguy]

Remove the ORDER BY clause.

 

[Sun_Force]

If I remove it, will it be sorted ASC?

 

[arnelgp]

how about if you Order by:

Order By Format([Delivery],"mm/dd")

 

[arnelgp]

SELECT Format([Delivery],"mm/dd") AS AD
FROM tblOrders WHERE Delivery Is Not Null AND Deleted=False
GROUP BY Format([Delivery],"mm/dd")

 

[theDBguy]

If I remove it, will it be sorted ASC?

I think that's the default.

 

[Sun_Force]

how about if you Order by:

Order By Format([Delivery],"mm/dd")

This one is giving me the same error

SELECT DISTINCT Format([Delivery],'mm/dd') AS AD
FROM tblOrders WHERE Delivery Is Not Null AND Deleted=False
ORDER BY Format([Delivery],"mm/dd");

 

[Sun_Force]

I think that's the default.

How about if I need it be DESC?

 

[arnelgp]

SELECT Format([Delivery],"mm/dd") AS AD
FROM tblOrders
WHERE (((tblOrders.[Delivery]) Is Not Null) AND ((tblOrders.[Deleted])=False))
GROUP BY Format([Delivery],"mm/dd")
ORDER BY Format([Delivery],"mm/dd");

 

[theDBguy]

How about if I need it be DESC?

You will probably need to use a subquery.

 

[Sun_Force]

SELECT Format([Delivery],"mm/dd") AS AD
FROM tblOrders WHERE Delivery Is Not Null AND Deleted=False
GROUP BY Format([Delivery],"mm/dd")

I can't understand how Groping can sort a field. How about if I need it be DESC order?

 

[Sun_Force]

SELECT Format([Delivery],"mm/dd") AS AD
FROM tblOrders
WHERE (((tblOrders.[Delivery]) Is Not Null) AND ((tblOrders.[Deleted])=False))
GROUP BY Format([Delivery],"mm/dd")
ORDER BY Format([Delivery],"mm/dd");

This one did the magic.
Million thanks.


Is there any reason a distinct field can not be sorted?

 

[arnelgp]

i have no idea why Distinct can't accept the sort order.

 

[arnelgp]

just now i check with Distinct and it worked!

SELECT DISTINCT Format([delivery],"mm/dd") AS Expr1
FROM tblOrders
WHERE (((tblOrders.[Delivery]) Is Not Null) AND ((tblOrders.[Deleted])=False))
ORDER BY Format([delivery],"mm/dd") DESC;

 

[Sun_Force]

just now i check with Distinct and it worked!

SELECT DISTINCT Format([delivery],"mm/dd") AS Expr1
FROM tblOrders
WHERE (((tblOrders.[Delivery]) Is Not Null) AND ((tblOrders.[Deleted])=False))
ORDER BY Format([delivery],"mm/dd") DESC;

Sorry. My mistake.
Seems that in #7 post above, I used 'mm/dd' instead of "mm/dd"
Hence the error.

Million thanks for your help.