Amazon Interview Question Production Engineers
The following SQL query would do the required task:
select stud_name from table where mark1=(select max(mark1) from table) or mark2=(select max(mark2) from table) or mark3=(select max(mark3) from table);
even it is a DB question, your answer is not correct.
1. You cannot assume there are only 3 subjects( in your SQL, you used mark1,2,3 for 3 subjects)
2. In each subject, there might be more than one highest scores. so you might not want to use the symbol '='
My answer for the SQL will be:
select * from table
where (subject, score)
in
(
select subject , max(score)
from table
group by subject )
The sub-query lists out : the data set of subject and max(score) for each subject.
then use the result combination feeding the main query for the record matching highest score in each subjects
sort the file using 'sort -rk 2,3 filename' and then take the first line from each
subject.
it seems the rest wrote crap. you anwered smartly. adding to your answer to pick the last two
sort -rk 2, 3|head -n2
and this should give the required answer. done!
Question states "get the student from each subject who secured the highest mark". This solution will give us the student (or 2 students) who secured the highest mark from the subject that is first in reverse alphabetical order. Also reversing the order (-r option) makes sense only if marks are numerical and the highest mark is better. In US however, marks are represented by the alphabetical letters like A,B,C,D,E and A (lowest) is the best. I think that correct answer could be something like this:
cat filename | sort -k 2,3 | awk '{if($2 != prev) {print; prev = $2;}}'
Why is this filed under "Linux Kernel"?
- LOLer July 15, 2009