joejoe joejoe - 4 months ago 14
SQL Question

How to display only value that occurs second highest number of times?

Write a query to display the customer name who visited the second highest number of times

select customer_id,count(*) from booking group by customer_id ;


using this query i got the count of number of visits for each customer as shown below

CUSTOMER_ID,COUNT(*)
C001,6
C002,1
C003,1
C004,1
C005,4


but i want to display only c005 since he has visited the second maximum time

Answer
SELECT customer_id, COUNT(*)
FROM booking
GROUP BY customer_id
HAVING COUNT(*) <> (SELECT MAX(t.custCount)
                    FROM (SELECT COUNT(*) AS custCount
                          FROM booking
                          GROUP BY customer_id) t )
ORDER BY COUNT(*) DESC
LIMIT 1

As a side note, this won't work if there are ties for second place. In this case, you use the above query as a condition in the WHERE clause, e.g.

SELECT customer_id
FROM booking
GROUP BY customer_id
HAVING COUNT(*) = (query given above)