We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
good one. i just did "Order by grade desc, name asc, marks asc" because there was not rule tha tmarks should be be sorted ascending otherwise so you do not really need the Decode rule. I used a temp teable to store the internal results first
select if(grades.grade<8,concat("NULL"),students.name),grades.grade,students.marks
from students inner join grades
on(students.marks>=grades.min_mark and students.marks<=grades.max_mark
)
group by grades.grade desc, students.name,students.marks desc;
the query say that students marks greater than or eql minimum mark and less than or eql ...see the grade table then think about input and output ....thats make u clear ...Happy Coding!!!
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks
from students,grades
where marks between min_Mark and Max_Mark
order by grade desc, name, coalesce(name,marks);
there is no need to give orderby name 2 times one outside an other inside coalesce
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from students,grades where marks between min_Mark and Max_Mark order by grade desc, coalesce(name,marks);
don't get confuse if you don't see an 'asc' keyword in many above soln. here r 2 query both identical and do same work
select (case when g.grade > 7 then s.name else Null end), g.grade, s.marks
from students s, grades g
where s.marks between g.min_mark and g.max_mark
order by g.grade desc, s.name, s.marks asc
select (case when g.grade > 7 then s.name else Null end), g.grade, s.marks
from students s, grades g
where s.marks between g.min_mark and g.max_mark
order by g.grade desc, s.name, s.marks
select (case when grade >=8 then name else null end) name, grade, marks
from
(select s.name, g.grade, s.marks
from students s join grades g
on s.marks between g.min_Mark and g.Max_Mark)
order by grade desc, name;
The Report
You are viewing a single comment's thread. Return to all comments →
I guess a more accurate one would be (I'm using Oracle):
in this case, if two students have the same grade and name and their name is not null they won't be ordered by their marks.
what is the use of using decode
Using decode, you order by marks only when the name is NULL.
that's genius man
good one. i just did "Order by grade desc, name asc, marks asc" because there was not rule tha tmarks should be be sorted ascending otherwise so you do not really need the Decode rule. I used a temp teable to store the internal results first
ERROR at line 1: ORA-00905: missing keyword ERROR IS THERE
can you please explain "where marks between min_Mark and Max_Mark" this part
Here is my MYSQL Soln:
select if(grades.grade<8,concat("NULL"),students.name),grades.grade,students.marks from students inner join grades on(students.marks>=grades.min_mark and students.marks<=grades.max_mark ) group by grades.grade desc, students.name,students.marks desc;
@tasfiahm
Thanks
this will also work for mysql
select (case when grade <8 THEN NULL ELSE name END) name, grade, marks from students,grades where marks between min_Mark and Max_Mark order by grade desc, name, coalesce(name,marks);
@rupalvshinde the coalesce in the end might not work as both the column name and marks have different data type.
why use coalese() function
Thanks for the solution. What I learnt: '>' != '>=' :)
isn't it should be marks asc? because
there is no need to give orderby name 2 times one outside an other inside coalesce
Thank you ji.
Helpful. Thank you!
don't get confuse if you don't see an 'asc' keyword in many above soln. here r 2 query both identical and do same work
I made some changes, I thint this is more clear. The only issue with this question is the order by. Thx for explaining decode.
What is the use of Decode function ?
select (case when grade >=8 then name else null end) name, grade, marks from (select s.name, g.grade, s.marks from students s join grades g on s.marks between g.min_Mark and g.Max_Mark) order by grade desc, name;
without it query working fine ..
for those using mysql, instead of using decode, I used; ...order by grade desc, name, ifnull(name, marks)
Btw great code, thanks a lot.
I didn't know that we don't need to join tables to use information from these tables simutenously.
You're supposed to use Join