SQL - getting record with maximum value

Question

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.

Which database please? the features and syntax for solutions differ by platform — Paul Maxwell
– Paul Maxwell, Commented May 10, 2016 at 7:53
LIMIT implies MySQL, but [Products] implies MS SQL Server. — jarlh
– jarlh, Commented May 10, 2016 at 8:15

Arulkumar · Accepted Answer · 2016-05-10 07:53:35Z

1

Use MAX and GROUP BY

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

answered May 10, 2016 at 7:53

Arulkumar

13.3k15 gold badges51 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chun Yin · Accepted Answer · 2016-05-10 07:55:08Z

0

I've always done it with the following

SELECT top 1 Name
FROM tableName
ORDER BY Price DESC

answered May 10, 2016 at 7:55

Chun Yin

2903 silver badges15 bronze badges

Comments

Kim · Accepted Answer · 2016-05-10 08:13:49Z

0

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

answered May 10, 2016 at 8:13

Kim

8258 silver badges25 bronze badges

Comments

reza.cse08 · Accepted Answer · 2016-05-10 08:24:57Z

0

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

edited May 10, 2016 at 8:24

answered May 10, 2016 at 8:13

reza.cse08

6,18651 silver badges42 bronze badges

2 Comments

jarlh Over a year ago

This will return all products, OP only wants the most expensive one.

reza.cse08 Over a year ago

sorry for misunderstanding. please check now

Collectives™ on Stack Overflow

SQL - getting record with maximum value

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related