CODEWITHSUNDEEP
sqlsql-server
Blake
Blake

Reputation: 35

How would you retrieve an entire column

ID     Name    Price
--------------------------------------
1      item1     10
2      item2     40
3      item3     10
4      item4     20
5      item5     50
6      item6     20

Say you had this table above and wanted to retrieve the following:

ID    Name    Price
5     item5    50

But you wanted to retrieve the above by using the highest price. I am currently using the below code.

SELECT 
    MAX(price) AS Price,
    Name,
    ID
FROM 
    ExampleTable
GROUP BY 
    Name, ID;

Upvotes: 0

Views: 38

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

In SQL Server we can try:

SELECT TOP 1 *
FROM yourTable
ORDER BY Price DESC;

If there could be more than one record tied for the highest price, and you also wanted to report all ties, then we could use WITH TIES:

SELECT TOP 1 WITH TIES *
FROM yourTable
ORDER BY Price DESC;

If you want to use TOP to select only certain columns, then just list those columns out, e.g.

SELECT TOP 1 ID, Price

to select only the ID and Price columns.

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use top as your DBMS is sql server

    select top 1 * from your_table
    order by Price desc

You could also use window function

with t1 as
(
select * , row_number() over(order by Price desc) as rn from your_table
) select ID  ,Name ,Price from t1 where rn=1

Upvotes: 1

Related Questions