CODEWITHSUNDEEP
mysqlsql
jmenezes
jmenezes

Reputation: 1926

Select records and order by scores desc

From the following data, how can I select all the children a parent has ordered by the perCent desc of those children?

Parents are type 3 only.

I tried the below, but that orders the entire result set. I need to order by each parents childrens perCent. You'll see in the expected results:

select b.id, b.type, b.perCent from scores a 
INNER JOIN scores b on a.id = b.parent and a.type=3 order by b.perCent desc;

Main Table

"id"    "type"  "parent"    "country"   "votes" "perCent"
"24"    "1"     "1"         "US"        "30"    "0"
"25"    "3"     "24"        "US"        "30"    "0"
"26"    "10"    "25"        "US"        "15"    "50.00"
"27"    "10"    "25"        "US"        "10"    "33.33"
"28"    "10"    "25"        "US"        "5"     "16.66"

"29"    "1"     "1"         "US"        "50"    "0"
"30"    "3"     "29"        "US"        "50"    "0"
"31"    "10"    "29"        "US"        "20"    "40.00"
"32"    "10"    "29"        "US"        "15"    "25.00"
"33"    "10"    "29"        "US"        "15"    "35.00"

Expected results:

"id"    "perCent" //Ordered by each parents childrens perCent desc
"26"    "50.00"
"27"    "33.33"
"28"    "16.66"

"31"    "40.00"
"33"    "35.00"
"32"    "25.00"

Upvotes: 0

Views: 90

Answers (3)

lordkain
lordkain

Reputation: 3109

select id,perCent from scores 
where parent in (select id from scores where type=3)
order by parent, percent desc

Upvotes: 1

user2864740
user2864740

Reputation: 61875

Keep the same general join, but apply an order by across multiple columns:

select child.id as childId, child.perCent
from scores parent
join scores child
   on parent.id = child.parent
where parent.type = 3
order by parent.id, child.perCent desc

This says, first order by the parent (such that all children for the parent will appear together) and then order by each child's (per said parent) percent descending.

Note that even though the parent.id column wasn't selected, it is still eligible for ordering.

Upvotes: 1

Damodaran
Damodaran

Reputation: 11047

Try this 

select GROUP_CONCAT(id),GROUP_CONCAT(type),GROUP_CONCAT(parent) from atab where type = 10 group by parent  order by id

Here you will get comma separated values sorted by id SQLFiddle

Upvotes: 0

Related Questions