hey folks!

i have the followiong table example:

ID----Book-------Category
======================

1          e                 b
2          d                  b
3          w                 a
4          t                  a
5          e                  c

what i'd like to do is get the Distinct categories, but sort them as they are in the DB, ie: according to the ID, to get this:

b
a
c

 but when i write :

"SELECT DISTINCT category FROM tbl ORDER BY ID ASC", i get an error, saying that the "order" clashes with the "Distinct" .

anyone got ideas how to do this>?

thanks!
Snooper

thanks for the reply.

i get this error:

You tried to execute a query that does not include the specified expression 'category' as part of an aggregate function.

ok, tried this, and at least i get no errors... but what i get now, is *all* the categories, sorted ASC - so the DISTINCT or ID havent done anything.

(i got this:
a
a
b
b
c )

lets keep at this... we'll (you'll!) get it in the end :)

What you are trying to do is impossible because you cannot sort by a column that is not in the aggregate or Group by clause. That means, which ID should SQL take as you are grouping by the category, the first, fifth .... ??? I hope you understand what I mean. You option is either to sort by the Category or put ID into an aggregate function like
Select Sum(ID), Category from tbl Group By Category ORDER by ID

there is no real nead in using DISTINCT in what you are trying to do but basically with the table you have the only option would be

select DISTINCT(Category) from tbl ORDER BY CATEGORY

Thanks for the reply, michael. i kinda see what you're saying..

option 2 gives:

a
b
c
(which is close, but no cigar)

option 1 gives the same error as before: You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

anyways, will keep at it....
THanks
sn

well hello Morningz...

if its so simple, how come you didnt manage to help me with the correct answer in your first 2 messages??

and FYI, been there and done that. neither of these site addresses this issue.

"snotty" - i dont think so...

but if this is what you call "help" - i'll leave it for sure.

This forum is not meant to help people, as i have been helped, and have tried to help, many times before. this is not a "take a hike, you didnt do your homework" style forum, its meant to help people out.

if you feel you dont want to bother helping, or that you want to guise your inablility to help as something else - then please dont post answers. if you have a direct link to a helpful resource, then by all means, post it. but dont lecture people. with a reply like that - i would have prefered to get no replies at all....

there are many other fine folks here on the forum that actually WANT to help, so go find someone else to lecture to and be condescending to.

(..and if you still think this is snotty - go look it up..)

No, you're right Morningz.

Snooper, those sights are excellent references to help you learn the basics of set theory (SQL Statements).

Once you understand this, you will understand that what you are trying to do, can't be done this way.

That said, IF the ID is an incrementing key and you want to use the minimum ID of a group of categories, to order the records, then you can use something like this:

SELECT Category, Min(ID) AS MinOfID
FROM tbl
GROUP BY Category
ORDER BY Min(ID);

Cheers,
Andrew

The first option I gave, why is it not a cigar, it is the output you are looking for just not sorted by ID which is not possible in this type of query. The second option DOES work unless you pasted ID in there. The error message you wrote suggests that you have the field ID in there which you can't unless you put it into an aggregate function like Bunce said. Nevertheless it does not make sense to use Min function as this is a fake sorting, why would you sort by ID anyway?

to michael: i want to sort by the ID, because i want the records displayed in the entry order, not by the ABC. ie i want: b>a>c and not a>b>c

i am still not sure WHY this is not do-able, but i ACCEPT it.

to Andrew: thanks! Your suggestion worked exactly as i wanted! :)

thanks to all the helpful folks who answered, and thats to Andrew for the winning answer!

Snooper

But you see its not necessarily in entry order.

What you are getting is the lowest ID for EACH group of categories, which in this case may be the earliest entry for each category.

You have to remember that once you GROUP by a value then you are summarising all records for that group into a SINGLE record. So by grouping by Category, you need to work out what you want to do with any other fields you wish to summarise.

In this case weve taken a MINIMUM of all the ID's for each group, and sorted by this.

Personally, I would be adding a 'Date Created' field and using this as it's more meaningful that using the ID.

Hope this makes it a little clearer.

Cheers,
Andrew

ok, i do get it now.
thanks for the clear explanation, and again for the solution

:)
sn