CODEWITHSUNDEEP
mysql
Ajay
Ajay

Reputation: 23

MySQL query for joining two queries

I have a mysql table having register number, dob, student name, sex, total mark in a semester in each row. I would like to get records of 10 Boys and 5 girls in descending order of total marks in a single SQL query.

Upvotes: 1

Views: 97

Answers (3)

Alex
Alex

Reputation: 23290

My MySQL dialect is rusty but this should do the trick

(SELECT * FROM Students WHERE sex = 'Male' ORDER BY TotalMarks DESC LIMIT 10)
UNION ALL
(SELECT * FROM Students WHERE sex = 'Female' ORDER BY TotalMarks DESC LIMIT 5)

It's a single query, mind you.

Upvotes: 1

Uttara
Uttara

Reputation: 2532

try this 

    select * from
    (select reg_number, dob, student_name, sex, total_marks where sex='male' 
    order by total_marks desc limit 10
    union
    select reg_number, dob, student_name, sex, total_marks where sex='female'
    order by total_marks desc limit 5) a order by total_marks desc

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33542

Try this code:

select 
    a.studentID,
    a.studentName,
    a.dob,
    a.totalMark
from
(
    select
        studentID,  
        studentName,
        dob,
        totalMark
    from
        students
    where
        sex='M'
    order by 
        studentMark desc
    limit 10
    union all
    select
        studentID,  
        studentName,
        dob,
        totalMark
    from
        students
    where
        sex='F'
    order by 
        totalMark desc
    limit 5
)
order by
    totalMark desc

Upvotes: 0

Related Questions