CODEWITHSUNDEEP
sqlmax
Loïs Talagrand
Loïs Talagrand

Reputation: 836

SQL - getting record with maximum value

I am trying to retrieve the product with the highest price:

SELECT ProductName, Price 
FROM [Products]
ORDER BY Price DESC
LIMIT 1

I wanted to know if there is another way of doing this in a more efficient way, with MAX for example.

Upvotes: 0

Views: 55

Answers (4)

reza.cse08
reza.cse08

Reputation: 6178

select top 1 * from [Products] order by Price desc

Upvotes: 0

Kim
Kim

Reputation: 825

You can use TOP 1 but you always have to consider the possibility of having a tie, so:

SELECT TOP 1 WITH TIES ProductName, Price 
FROM [Products]
ORDER BY Price DESC

Upvotes: 0

Chun Yin
Chun Yin

Reputation: 290

I've always done it with the following

SELECT top 1 Name
FROM tableName
ORDER BY Price DESC

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Use MAX and GROUP BY

SELECT ProductName, MAX(Price) [Price]
FROM [Products]
GROUP BY ProductName
ORDER BY MAX(Price) DESC
LIMIT 1;

Upvotes: 1

Related Questions