Web Wiz - Solar Powered Eco Hosting

  New Posts New Posts RSS Feed - SQL DISTINCT stuff
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Topic ClosedSQL DISTINCT stuff

 Post Reply Post Reply Page  123>
Author
snooper View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 23 April 2002
Location: Israel
Status: Offline
Points: 333
Direct Link To This Post Topic: SQL DISTINCT stuff
    Posted: 12 February 2003 at 11:03am

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

Back to Top
MorningZ View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
Direct Link To This Post Posted: 12 February 2003 at 11:08am
try:

SELECT DISTINCT(category), ID FROM tbl GROUP BY ID
Contribute to the working anarchy we fondly call the Internet
Back to Top
snooper View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 23 April 2002
Location: Israel
Status: Offline
Points: 333
Direct Link To This Post Posted: 12 February 2003 at 11:20am

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.

Back to Top
MorningZ View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
Direct Link To This Post Posted: 12 February 2003 at 11:26am
Oooops:
SELECT DISTINCT(category), ID FROM tbl GROUP BY ID, category
Contribute to the working anarchy we fondly call the Internet
Back to Top
snooper View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 23 April 2002
Location: Israel
Status: Offline
Points: 333
Direct Link To This Post Posted: 12 February 2003 at 11:54am

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 :)



Edited by snooper
Back to Top
michael View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4673
Direct Link To This Post Posted: 12 February 2003 at 10:35pm

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

Back to Top
snooper View Drop Down
Mod Builder Group
Mod Builder Group
Avatar

Joined: 23 April 2002
Location: Israel
Status: Offline
Points: 333
Direct Link To This Post Posted: 13 February 2003 at 6:56am

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

Back to Top
MorningZ View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
Direct Link To This Post Posted: 13 February 2003 at 7:57am
why dont you take a few minutes of your time and head over to SQL Team and/or W3 Schools and investigate this stuff yourself?

you're not advancing much as a programmer if you can't find these simple answers on your own

Edited by MorningZ
Contribute to the working anarchy we fondly call the Internet
Back to Top
 Post Reply Post Reply Page  123>
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.01
Copyright ©2001-2018 Web Wiz Ltd.


Become a Fan on Facebook Follow us on Twitter Connect with us on LinkedIn Community Forums Web Wiz Blogs Web Wiz News
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2019 Web Wiz Ltd. All rights reserved.