Interview Question
Country: India
It is correlated subquery.. we can also use
select * from books order by price desc limit(0,5);
but if the 5th price is repeated , the query wont consider..
but this correlated subquery uses the condition : price >= 5th expensive price
but how the '5th expensive price' is calculated? it is done by making use of count(*) :)
is it the answer?? : It selects a list of books which have no books greater than 5 in number with a greater price compared to itself.
Yes, the answer is : it will select 5 most expensive books.
But how the query is running i m confused. Can anyone explain
I can explain how this sentence works. u can take b and t as tuple, in this "select count(*) from book as t where t.price>b.price" we can get how many books's price is higher than t, including itself, then we will choose the book that there are less than five books' price higher than it. This is how this sentence works. I think it is b or t confused you, you can take it as a variable, and its value comes from sql table.
I dont think it will get you the 5 most expensive books because if you think about this again if there are say 15 books and all prices are different then (select count(*) from book as t where t.price>b.price) query is going to return 15 for most expensive query which is not less than 5. so I think this is going to return 5 lowest price books.
This gets the top 5 most expensive books, except the top 5 designation here will admit ties. In other words, if the books are:
- eugene.yarovoi January 14, 2013A $31.10
B $30.00
C $22.12
D $22.12
E $19.99
F $19.99
G $17.16
This query will select all books A-F because E and F are tied for 5th place. So 6 results will be returned. If, however, we now insert ('H', 22.12), the query will now return A, B, C, D, and H, since E and F will both be pushed to 6th place now.
I would note that this query is likely to be an inefficient way to achieve the result. There are much better ways to do this. In MS SQL Server for example, there's ready-made syntax to do this:
SELECT TOP 5 WITH TIES title
FROM Books
ORDER BY Price DESC
If you didn't have access to that, you a good way to do it would be to select the 5th highest price, and then query for all titles whose price is >= that price:
SELECT title
FROM Books
WHERE
Price >=
(SELECT TOP 1 Price FROM (SELECT TOP 5 Price FROM Books ORDER BY Price DESC) ORDER BY Price ASC)
As compared to the approach in this question statement, the advantage here is that the subqueries are independent of any per-row information, which will lead the SQL engine to evaluate the subquery once before beginning the main query, as opposed to evaluating it for each row.