gopigoppu gopigoppu - 6 months ago 19
SQL Question

What is wrong with this query with DISTINCT keyword?

I want to display

dishname
and
dishprice
with
DISTINCT
value of 2nd highest
dishprice
.

What is the problem with the following query?

SELECT dishName, DISTINCT(dishPrice) FROM `tblDish` ORDER BY dishPrice LIMIT 1,1

Answer

In case there are more then one record for same price then need to group them bye price and group concat all the records

SELECT GROUP_CONCAT(dishName), dishPrice FROM `tblDish` GROUP BY dishPrice ORDER BY dishPrice DESC LIMIT 1,1