How to select a specific row from an SQL query?-CodePudding

I have a sales database and I want to display the least used payment method. From the query that displays the times each payment method is used, I want to display the name of the method and how many times it was used only.

To display the payment methods and counts I used SELECT Payment, COUNT(Payment) AS Occ FROM SALES GROUP BY Payment ORDER BY Payment

This resulted in the following output:

Payment	Occ
Paypal	123
Credit	352

I wanted to display Paypal only so I used the following:

SELECT Payment, MIN(Occ) AS Frequency FROM (SELECT Payment, COUNT(Payment) AS Occ FROM SALES GROUP BY Payment ORDER BY Payment)

But that gave an error

The desired result is

Payment	Frequency
Paypal	123

CodePudding user response：

You can use LIMIT to obtain only 1 result. However, I have to note out that in your initial solution, you are ordering the results by the name of payment, not the count of payments. The default order is ASC, so it is not necessary to add it. Refer to the snippet below!

SELECT Payment, COUNT(Payment) AS Occ 
FROM SALES 
GROUP BY Payment 
ORDER BY COUNT(PAYMENT)
LIMIT 1

CodePudding user response：

Top 1 will do:

SELECT TOP 1 Payment, COUNT(*) AS Occ 
FROM SALES 
GROUP BY Payment 
ORDER BY COUNT(Payment)