SQL select with counting rows
Hi there,
I have a table like the one below(simplified). I want to query the database to get the top 10 entries that have most number of "Numbers". For example, in the table below 2345 appears 5 times, 1234 appears three times and 4546 appears two times. I want to get the resultant set as given at the bottom. What query command I can use? Thank you.
name |ID| Number
abc | ID1 | 1234
wsx| ID6| 3456
wds| ID7| 4546
wdz| ID8| 2345
ert| ID12| 2345
cde | ID5| 1234
wfs| ID9| 2345
ert| ID13| 8955
ert| ID14| 4546
csg| ID11| 2345
ert| ID15| 9821
cdh | ID4| 1234
asd| ID10| 2345
The query results should be with total ten entries.
wdz| ID8| 2345
ert| ID12| 2345
wfs| ID9| 2345
csg| ID11| 2345
asd| ID10| 2345
abc | ID1 | 1234
cde | ID5| 1234
cdh | ID4| 1234
wds| ID7| 4546
ert| ID14| 4546
I have a table like the one below(simplified). I want to query the database to get the top 10 entries that have most number of "Numbers". For example, in the table below 2345 appears 5 times, 1234 appears three times and 4546 appears two times. I want to get the resultant set as given at the bottom. What query command I can use? Thank you.
name |ID| Number
abc | ID1 | 1234
wsx| ID6| 3456
wds| ID7| 4546
wdz| ID8| 2345
ert| ID12| 2345
cde | ID5| 1234
wfs| ID9| 2345
ert| ID13| 8955
ert| ID14| 4546
csg| ID11| 2345
ert| ID15| 9821
cdh | ID4| 1234
asd| ID10| 2345
The query results should be with total ten entries.
wdz| ID8| 2345
ert| ID12| 2345
wfs| ID9| 2345
csg| ID11| 2345
asd| ID10| 2345
abc | ID1 | 1234
cde | ID5| 1234
cdh | ID4| 1234
wds| ID7| 4546
ert| ID14| 4546
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
By the way, I am using sqlite
>Thank you. What does " t_count ON t.Number = t_count.Number" do
I'm using a subquery named t_count to count the number of Numbes.
The JOIN .. ON joins that subquery to the main SELECT, so that I can use the count of Numbers in the ORDER BY clause.
>By the way, I am using sqlite
The code I posted was straight T-SQL, so if there's any differences between T-SQL and sqlite you'll have to explain for us, as I don't have that on my dev box.
I'm using a subquery named t_count to count the number of Numbes.
The JOIN .. ON joins that subquery to the main SELECT, so that I can use the count of Numbers in the ORDER BY clause.
>By the way, I am using sqlite
The code I posted was straight T-SQL, so if there's any differences between T-SQL and sqlite you'll have to explain for us, as I don't have that on my dev box.
ASKER
Thank you. I got it.
ASKER