How to get all the fields of a row using the SQL MAX function?

Posted by Yiannis Mpourkelis on Stack Overflow See other posts from Stack Overflow or by Yiannis Mpourkelis
Published on 2010-05-23T23:18:11Z Indexed on 2010/05/23 23:20 UTC
Read the original article Hit count: 263

Filed under:

sql

|

max

Consider this table (from http://www.tizag.com/mysqlTutorial/mysqlmax.php):

Id name type price 123451 Park's Great Hits Music 19.99 123452 Silly Puddy Toy 3.99 123453 Playstation Toy 89.95 123454 Men's T-Shirt Clothing 32.50 123455 Blouse Clothing 34.97 123456 Electronica 2002 Music 3.99 123457 Country Tunes Music 21.55 123458 Watermelon Food 8.73

This SQL query returns the most expensive item from each type: SELECT type, MAX(price) FROM products GROUP BY type

Clothing $34.97 Food $8.73 Music $21.55 Toy $89.95

I also want to get the fields id and name that belong to the above max price, for each row. What SQL query will return a table like this?

Id name type price 123455 Blouse Clothing 34.97 123458 Watermelon Food 8.73 123457 Country Tunes Music 21.55 123453 Playstation Toy 89.95

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More

Related posts about max

SQL Server: SELECT rows with MAX(Column A), MAX(Column B), DISTINCT by related columns

as seen on Stack Overflow - Search for 'Stack Overflow'
Scenario: Table A MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/1/2010, 'Fred', null, null 2, 1/2/2010, 'Barney', 'Mr. Slate', 1/7/2010 3, 1/3/2010, 'Noname', null, null Table B MasterID, Added Date, Added By, Updated Date, Updated By, 1, 1/3/2010, 'Wilma', 'The Great Kazoo',… >>> More
How do I determine if my controller is in IDE or AHCI mode in Linux?

as seen on Super User - Search for 'Super User'
I have an old MacBook Pro 4,1 (early 2008) - but I suspect an answer would apply to many MacBook Pros. It has an Intel IDE/SATA controller (ICH8M/ICH8M-E). I have installed a patched MBR that is supposed to put my controller into AHCI mode. It does this by setting some controller port value that… >>> More
Positioning an image inside an ImageView width max height and max width set

as seen on Stack Overflow - Search for 'Stack Overflow'
I have an ImageView with max height and max width both set to 100. The figure below is clearly not a square, but you can use your imagination ;) Figure 1: +----------------------------------------------+ ¦ ImageView +--------------+ ¦ ¦ ¦ ¦ … >>> More
Getting the index of the returned max or min item using max()/min() on a list

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm using Python's max and min functions on lists for a minimax algorithm, and I need the index of the value returned by max() or min(). In other words, I need to know which move produced the max (at a first player's turn) or min (second player) value. for i in range(9): newBoard = currentBoard… >>> More
Storing varchar(max) & varbinary(max) together - Problem?

as seen on Stack Overflow - Search for 'Stack Overflow'
I have an app that will have entries of both varchar(max) and varbinary(max) data types. I was considering putting these both in a separate table, together, even if only one of the two will be used at any given time. The question is whether storing them together has any impact on performance. Considering… >>> More