php - mysql: How can list 2 different condition, ordering, and limit requirements from one table? -

php - mysql: How can list 2 different condition, ordering, and limit requirements from one table? -

there table of men , women. table has field, sex has values, men or women.

tabel-a id sex grade-a grade=b 1 men 2 4 2 women 4 5 3 women 6 1 4 men 1 3 5 men 3 6 6 men 5 2

i want sort table below;

first, list 2 men ordering grade-a desc.

second, list rest both men , woman ordering grade-b desc

expected output;

id sex grade-a grade=b 6 men 5 2 5 men 3 6 2 women 4 5 1 men 2 4 4 men 1 3 3 women 6 1

because these 2 conditions have different ordering requirement not utilize union. union slow.

here failed query, part 1

select distinct t.* ( ( select * table-a a.sex = 'men' order a.grade-a desc limit 2 ) union ( select * table-a order a.grade-b desc ) ) t

also tried "case when" logic, cannot limit 2 men @ first,

here failed query, part 2

select * table-a order case when a.sex = 'men' a.grade-a end desc, a.grade-b desc

could back upwards create proper query this?

best regards

try query:

select a.* `table-a` left bring together ( select m2.id `table-a` m2 m2.sex = 'men' order m2.`grade-a` desc limit 2 ) m2 on m2.id = a.id order case when m2.id null 1 else 0 end, case when m2.id null a.`grade-b` else a.`grade-a` end desc

test on sql fiddle

explanation:

to understand how order by works, see @ table - here table-a info plus values of expressions in query (case1 refers first case expression, case2 - second):

id sex grade-a grade=b m2.id case1 case2 1 men 2 4 null 1 4 2 women 4 5 null 1 5 3 women 6 1 null 1 1 4 men 1 3 null 1 3 5 men 3 6 5 0 3 6 men 5 2 6 0 5

order by sorts rows case1 values (in ascending order) , case2 (in descending). after sort have expected:

id sex grade-a grade=b m2.id case1 case2 6 men 5 2 6 0 5 5 men 3 6 5 0 3 2 women 4 5 null 1 5 1 men 2 4 null 1 4 4 men 1 3 null 1 3 3 women 6 1 null 1 1

more info sorting rows

php mysql sql mysql-error-1064