December 10, 2015 at 10:43 am
Hello,
I have the following question:
My data set is:
id: - number:
1 - 20
2 - 11
3 - 20
4 - 8
5 - 11
6 - 4
First, I want to group by number and order by the count of that group. So I have the following select statement:
SELECT number, count(*) as numbercount FROM numbers group by number order by numbercount desc
The result SHOULD be:
number: - numbercount:
20 - 2
11 - 2
8 - 1
4 - 1
So I would like to order by numbercount desc. That works, but after that, I would like to order by the smallest id that exists in that group. Number 20 is the first number that counts 2, so number 20 must be the first result. After that, number 11 counts 2, so it must be after number 20.
The problem is that I just don't know how to order that groups with the same numbercount.
I hope I explained my problem clear.
Thank you very much in advance!
December 10, 2015 at 10:47 am
Perhaps this?
CREATE TABLE #Numbers (ID int, Number int)
INSERT INTO #Numbers
VALUES (1,20),(2,11),(3,20),(4,8),(5,11),(6,4)
SELECT number, COUNT(*) AS NumberCount
FROM #Numbers
GROUP BY Number
ORDER BY NumberCount desc, MIN(ID) asc
DROP TABLE #Numbers
Cheers!
December 10, 2015 at 11:22 am
Thank you for your reply.
Unfortunately the addition of MIN(id) ASC does not the trick. It's not always working 🙁
For your info: It is MySQL (if that matters).
December 10, 2015 at 11:48 am
Frank Dijk (12/10/2015)
For your info: It is MySQL (if that matters).
Yes it does. This is a Microsoft SQL Server forum. We know MS SQL, not MySQL, so solutions posted here probably won't work on MySQL. Maybe try the http://forums.mysql.com?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2015 at 12:08 pm
Frank Dijk (12/10/2015)
Thank you for your reply.Unfortunately the addition of MIN(id) ASC does not the trick. It's not always working 🙁
For your info: It is MySQL (if that matters).
As Gail said, you'll probably get better help from a dedicated MySQL forum. Having said that, I've dabbled in MySQL, and that solution should work as far as I know (a quick MySQL fiddle confirmed that it works for the sample data provided).
My curiosity's piqued, so I'd be interested in knowing how it fails and on what data. If you could provide the problematic data set in the form of CREATE TABLE statements and INSERTs, along with the exact query you're running, that would be splendid.
Cheers!
December 10, 2015 at 2:09 pm
######
December 10, 2015 at 2:25 pm
As I understand your requirement, that should be the correct result.
You can see that by adding MIN(ID) to the column list returned by the SELECT.
| getal | aantalmaal | MIN(ID) |
|-------|------------|---------|
| 11 | 9 | 79 |
| 19 | 9 | 87 |
| 36 | 8 | 78 |
| 13 | 8 | 113 |
If you insert another 36, then it will be tied with 11 and 19 at 9 occurrences. Its MIN(ID) is 78, lower than 11's (79) and 19's (87).
Since you want the number with the lowest MIN(ID) to show up first in case of ties, that result seems correct.
Perhaps I'm misunderstanding your requirements?
Cheers!
EDIT: Now that I think about it, perhaps you meant that when more than one number occurred N times, you wanted the number whose Nth occurrence had the lowest ID? In that case, it would just be a matter of changing MIN to MAX.
December 10, 2015 at 2:35 pm
Thank you very much Jacob.
I think I found it with your help. I changed MIN to MAX(id) and that worked.
Adding the column is helpful indeed.
Thank you again!
December 10, 2015 at 2:37 pm
Frank Dijk (12/10/2015)
Thank you very much Jacob.I think I found it with your help. I changed MIN to MAX(id) and that worked.
Adding the column is helpful indeed.
Thank you again!
I just had the same thought and was editing my previous post to reflect that while you posted. 🙂
I'm glad you figured it out!
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply