CODEWITHSUNDEEP
sqlsql-server
timval
timval

Reputation: 23

Selecting a piece of data from a table, with the highest value in SQL 2014

Problem: I need to display the name, price, and alcohol content of a piece of data that is the highest of its kind in a certain row in my table.

Attempted Solution: 

SELECT MAX(wineSugar_gl) FROM WINE;

Where WINE is the table name and wineSugar_gl is the row name.

Output from attempted solution: Attempted solution output

Question: What would I add to this code in order to display the rest of the data needed?

Additional Information: Below are the row names that I need to display.

wineName
winePrice
wineAlchohal

Upvotes: 0

Views: 45

Answers (2)

SlimsGhost
SlimsGhost

Reputation: 2909

If you want to get some aggregate value (like MAX()), and you want other fields from the same row, a very popular and efficient method is to use the row_number() function, like this:

SELECT wineName, winePrice, wineAlchohal, wineSugar_gl 
FROM (
     SELECT wineName, winePrice, wineAlchohal, wineSugar_gl,
         row_number() over (order by wineSugar_gl desc) as seqnum
     FROM WINE
) x
where seqnum = 1 
--since we ordered by wineSugar_gl descending, seqnum 1 is equivalent 
--to max(wineSugar_gl), and you can also get other fields from that row

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can try like this

SELECT wineName, winePrice, wineAlchohal, MAX(wineSugar_gl) FROM WINE group by wineName, winePrice, wineAlchohal

Upvotes: 1

Related Questions