SQL select odd rows after sorting by multiple columns in different order-CodePudding

I have a students table(id, score, department) and departments table(id, name). I'm trying to add certain conditions to the query.

School_dept

 --------- ------------- 
| dept_id | name        |
 --------- ------------- 
| 1       | Admin       |
 --------- ------------- 
| 2       | Chemistry   |
 --------- ------------- 
| 3       | Physics     |
 --------- ------------- 
| 4       | Biology     |
 --------- ------------- 
| 5       | Mathematics |
 --------- -------------

Students

 ------------ ------- ------------ 
| student_id | score | department |
 ------------ ------- ------------ 
| 26         | 11    | 4          |
 ------------ ------- ------------ 
| 34         | 11    | 3          |
 ------------ ------- ------------ 
| 76         | 11    | 2          |
 ------------ ------- ------------ 
| 49         | 11    | 1          |
 ------------ ------- ------------ 
| 38         | 11    | 5          |
 ------------ ------- ------------

select all school_depts with less than 5 students
sort the school departments by total score of students in descending order. If there is a tie then get the highest number of students in the dept will be first; if there still a tie then dept with smallest dept_id should be first.
consider only odd rows and exclude even rows

my attempt

SELECT * FROM (
SELECT * , ROW_NUMBER() OVER() AS ROW_ID FROM (
SELECT dept_id, name, count(E.student_id) as total_students, SUM(score) as total_score
from Students E
LEFT JOIN school_dept D on dept_id=student_id
group by 1,2
) as O
WHERE total_students<3
order by total_score desc, total_students desc, dept_id asc
) as U
WHERE ROW_ID %2 <>0

Expected Output is

name, total_students, total_score
Admin,1,11
Physics1,11
Mathematics,1,11

http://sqlfiddle.com/#!17/248eb8/2

CodePudding user response：

I think there is a join issue in your query (joining student_id with dept_id), and you are selecting for less than three students (but the requirement says less than five students).

Please try the following:

select department as "name", total_students, total_score
from
(
    select *, row_number() over (order by total_score desc, total_students desc, dept_id) row_id
    from
    (
        select sum(score) total_score, count(student_id) total_students, sd.name department, sd.dept_id
        from students s
        join school_dept sd on s.department = sd.dept_id
        group by 3,4
        having count(student_id) < 5
    )t
)tt
where row_id%2 <> 0

Please see the SQL Fiddle here.

CodePudding user response：

As School_dept is parent table for department So JOINING will School_dept Left join Students. Because a department may have no students so left join is the perfect. But If consider those departments which has students then use INNER JOIN.

-- SQL SERVER (v2017)
SELECT p.dept_name "Name", p.total_students, p.total_score
FROM (SELECT *
           , ROW_NUMBER() OVER (ORDER BY t.total_score DESC, t.total_students DESC, t.dept_id) row_num
      FROM (SELECT sd.dept_id
                 , MAX(sd.name) dept_name
                 , COUNT(s.student_id) total_students
                 , SUM(s.score) total_score
            FROM School_dept sd
            LEFT JOIN Students s
                   ON sd.dept_id = s.department
            GROUP BY sd.dept_id
            HAVING COUNT(s.student_id) < 5) t) p
WHERE (p.row_num % 2) != 0;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=abf4b40f692d085b98054aa9c9a1dcc7

CodePudding user response：

I think below query should work

select *,
Rank() over  (order by Score desc,TotalStudent desc,dept_id asc) as Serial
from
(
select d.dept_id,d.Name,COUNT(student_id) TotalStudent,sum(Score) Score

from Students s
left join School_dept d on s.department=d.dept_id
where d.dept_id % 2 <> 0
group by d.Name,d.dept_id
) a where TotalStudent < 5