I need some SQL query help, please

Factual Questions
1

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

You shouldn’t need to perform any kind of join to get this information.

The following query should do it:


select ID, FruitName
from dbo.Fruit
group by ID, FruitName
order by FruitName, ID
3

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:


select ID, FruitName
from dbo.Fruit
group by ID, FruitName
**order by count(FruitName)** desc
4

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

Oops, simulpost. That looks much better!

6

Forget it - that won’t work either. I just tried it out locally. Sorry about that.

Apparently, my brain doesn’t work over lunch. :frowning:

7

Ok, here we go. I’ve finished my lunch, so my brain doesn’t have an excuse this time.


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

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

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

Oops… Well, forget my offering then.

11

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:


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

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

You’re welcome.

Zev Steinhardt