Reply
 
Thread Tools Display Modes
  #1  
Old 03-18-2009, 04:41 PM
tdn tdn is offline
Guest
 
Join Date: Feb 2000
Posts: 35,871
I need some SQL query help, please

I know this can be done, but I'm having a luddite moment here.

create table dbo.Fruit (
ID int identity,
FruitName varchar(20) not null
)

INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Grape')
INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Orange')
INSERT INTO dboFruit VALUES ('Grape')
INSERT INTO dboFruit VALUES ('Banana')

I want to return this set:

1 Banana
2 Banana
4 Banana
7 Banana
3 Grape
6 Grape
5 Orange

Note the order -- the most "popular" entry comes first. I suspect that I will need a self-join, and the clause ORDER BY COUNT(FruitName) DESC.

Ideas?
  #2  
Old 03-18-2009, 04:58 PM
LilShieste LilShieste is offline
Guest
 
Join Date: Dec 2001
Location: CA East Bay
Posts: 1,623
You shouldn't need to perform any kind of join to get this information.

The following query should do it:
Code:
select ID, FruitName
from dbo.Fruit
group by ID, FruitName
order by FruitName, ID
  #3  
Old 03-18-2009, 05:04 PM
LilShieste LilShieste is offline
Guest
 
Join Date: Dec 2001
Location: CA East Bay
Posts: 1,623
Ack! Sorry, tdn, I screwed that up.

To order the rows correctly, you would use the clause that you suspected. So, the query is actually:
Code:
select ID, FruitName
from dbo.Fruit
group by ID, FruitName
order by count(FruitName) desc
  #4  
Old 03-18-2009, 05:04 PM
tdn tdn is offline
Guest
 
Join Date: Feb 2000
Posts: 35,871
Thanks, but that won't do it. It would order the fruit alphabetically, not by number of rows. (That probably wasn't clear from my example, come to think of it). If I had 5 entries for oranges and 3 for bananas, I'd want oranges to come first.

And, contrary to my example, I don't actually have an ID field, and there may well be duplicates, so grouping might cause problems.
  #5  
Old 03-18-2009, 05:05 PM
tdn tdn is offline
Guest
 
Join Date: Feb 2000
Posts: 35,871
Oops, simulpost. That looks much better!
  #6  
Old 03-18-2009, 05:07 PM
LilShieste LilShieste is offline
Guest
 
Join Date: Dec 2001
Location: CA East Bay
Posts: 1,623
Forget it - that won't work either. I just tried it out locally. Sorry about that.

Apparently, my brain doesn't work over lunch.
  #7  
Old 03-18-2009, 05:21 PM
LilShieste LilShieste is offline
Guest
 
Join Date: Dec 2001
Location: CA East Bay
Posts: 1,623
Ok, here we go. I've finished my lunch, so my brain doesn't have an excuse this time.

Code:
select a.ID, a.FruitName
from dbo.Fruit a
 join dbo.Fruit b on b.FruitName = a.FruitName
group by a.ID, a.FruitName
order by count(b.FruitName) desc
IOW, all of your suspicions were true.
  #8  
Old 03-18-2009, 05:24 PM
zev_steinhardt zev_steinhardt is offline
Charter Member
 
Join Date: Jan 2000
Location: Brooklyn, NY
Posts: 6,900
Quote:
Originally Posted by tdn View Post
I know this can be done, but I'm having a luddite moment here.

create table dbo.Fruit (
ID int identity,
FruitName varchar(20) not null
)

INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Grape')
INSERT INTO dboFruit VALUES ('Banana')
INSERT INTO dboFruit VALUES ('Orange')
INSERT INTO dboFruit VALUES ('Grape')
INSERT INTO dboFruit VALUES ('Banana')

I want to return this set:

1 Banana
2 Banana
4 Banana
7 Banana
3 Grape
6 Grape
5 Orange

Note the order -- the most "popular" entry comes first. I suspect that I will need a self-join, and the clause ORDER BY COUNT(FruitName) DESC.

Ideas?

There's always this approach:

SELECT fruitname, count(fruitname) cnt
INTO #temp
FROM fruit
GROUP BY fruitname

SELECT f.fruitname
FROM fruit f
JOIN #temp t
ON f.fruitname = t.fruitname
ORDER BY t.cnt desc

Zev Steinhardt
  #9  
Old 03-18-2009, 05:31 PM
Keeve Keeve is offline
Guest
 
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,070
Access gave me this:

SELECT Fruit_1.ID, Fruit_1.FruitName
FROM Fruit INNER JOIN Fruit AS Fruit_1 ON Fruit.FruitName = Fruit_1.FruitName
GROUP BY Fruit.FruitName, Fruit_1.ID, Fruit_1.FruitName
ORDER BY Count(Fruit.FruitName) DESC , Fruit_1.ID;
  #10  
Old 03-18-2009, 05:32 PM
Keeve Keeve is offline
Guest
 
Join Date: Aug 2000
Location: NY/NJ, USA
Posts: 5,070
Quote:
Originally Posted by tdn View Post
And, contrary to my example, I don't actually have an ID field, and there may well be duplicates, so grouping might cause problems.
Oops... Well, forget my offering then.
  #11  
Old 03-18-2009, 05:49 PM
LilShieste LilShieste is offline
Guest
 
Join Date: Dec 2001
Location: CA East Bay
Posts: 1,623
Quote:
Originally Posted by Keeve View Post
Oops... Well, forget my offering then.
Thanks for pointing that out, Keeve.

In this case, you will either have to make use of a subquery, or use the approach that zev suggested above. Here is the subquery approach:
Code:
select a.FruitName
from dbo.Fruit a
order by
 (select count(b.FruitName)
  from dbo.Fruit b
  where b.FruitName = a.FruitName     
  group by b.FruitName) desc
  #12  
Old 03-19-2009, 12:45 PM
tdn tdn is offline
Guest
 
Join Date: Feb 2000
Posts: 35,871
Zev nailed it. I tried all of the other suggestions, and for some reason they all failed. I burned a lot of time trying to make them work, but somehow they didn't.

But the temp table thing? Works like a charm.

Thank you!
  #13  
Old 03-20-2009, 02:36 PM
zev_steinhardt zev_steinhardt is offline
Charter Member
 
Join Date: Jan 2000
Location: Brooklyn, NY
Posts: 6,900
Quote:
Originally Posted by tdn View Post
Zev nailed it. I tried all of the other suggestions, and for some reason they all failed. I burned a lot of time trying to make them work, but somehow they didn't.

But the temp table thing? Works like a charm.

Thank you!
You're welcome.

Zev Steinhardt
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 04:50 AM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2017 Sun-Times Media, LLC.

 
Copyright © 2017