# I need some SQL query help, please
**URL:** https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939
**Category:** Factual Questions
**Created:** [March 18, 2009, 8:41pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939 "2009-03-18T20:41:09Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2009, 8:41pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/1 "2009-03-18T20:41:09Z")
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?
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [March 18, 2009, 8:58pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/2 "2009-03-18T20:58:46Z")
You shouldn’t need to perform any kind of join to get this information.
The following query should do it:
```auto
select ID, FruitName
from dbo.Fruit
group by ID, FruitName
order by FruitName, ID
```
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [March 18, 2009, 9:04pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/3 "2009-03-18T21:04:29Z")
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:
```auto
select ID, FruitName
from dbo.Fruit
group by ID, FruitName
**order by count(FruitName)** desc
```
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2009, 9:04pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/4 "2009-03-18T21:04:47Z")
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.
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 18, 2009, 9:05pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/5 "2009-03-18T21:05:51Z")
Oops, simulpost. That looks much better!
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [March 18, 2009, 9:07pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/6 "2009-03-18T21:07:39Z")
Forget it - that won’t work either. I just tried it out locally. Sorry about that.
Apparently, my brain doesn’t work over lunch. ☹
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [March 18, 2009, 9:21pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/7 "2009-03-18T21:21:27Z")
Ok, here we go. I’ve finished my lunch, so my brain doesn’t have an excuse this time.
```auto
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.
---
### Author:  [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)
#### Post date: [March 18, 2009, 9:24pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/8 "2009-03-18T21:24:08Z")
> [@tdn](#):
>
> 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
---
### Author:  [@Keeve](https://boards.straightdope.com/u/Keeve)
#### Post date: [March 18, 2009, 9:31pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/9 "2009-03-18T21:31:22Z")
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;
---
### Author:  [@Keeve](https://boards.straightdope.com/u/Keeve)
#### Post date: [March 18, 2009, 9:32pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/10 "2009-03-18T21:32:45Z")
> [@tdn](#):
>
> 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.
---
### Author:  [@LilShieste](https://boards.straightdope.com/u/LilShieste)
#### Post date: [March 18, 2009, 9:49pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/11 "2009-03-18T21:49:22Z")
> [@Keeve](#):
>
> 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:
```auto
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
```
---
### Author:  [@tdn](https://boards.straightdope.com/u/tdn)
#### Post date: [March 19, 2009, 4:45pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/12 "2009-03-19T16:45:16Z")
**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!
---
### Author:  [@zev\_steinhardt](https://boards.straightdope.com/u/zev_steinhardt)
#### Post date: [March 20, 2009, 6:36pm UTC](https://boards.straightdope.com/t/i-need-some-sql-query-help-please/489939/13 "2009-03-20T18:36:33Z")
> [@tdn](#):
>
> **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