0

I have searched desperately for the answer to a seemingly easy query to pull off- but no luck in finding the answer so far. I am hoping someone can at least point me in the right direction.

Say I have a table with rope colors and sizes (inches) with the columns: color, inches.

If the values in color and inches, respectively, are:

Red          38
Red          45
Yellow       12
Blue         85
Blue         12
Blue         18

I want to query for the longest rope of a single color. Ideally, the query would return:

Red        45
Yellow     12
Blue       85

How could I search for this using SQL queries?

Thank you all!

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
Blazed
  • 11
  • 3
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – jmargolisvt Feb 22 '16 at 22:43

1 Answers1

3

You need to research GROUP BY statement and read about aggregate functions.

MySQL Reference manual can be a great source of knowledge in this case.

select
  color, max(inches) as longest_inches
from
  yourtable
group by color

It works by grouping all rows with the same value in column color and then retrieving the maximum value amongst each group thus giving you the expected output

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
  • If we also want to return other columns from the row which has the maximum value, the query required for that is less trivial. For the result OP specified, this is the pattern we'd use. – spencer7593 Feb 22 '16 at 23:06
  • Extending @spencer7593 comment, if there was more than 1 row with max value in a group and we would also want to retrieve another column, all rows for a particular group would be returned in the result. – Kamil Gosciminski Feb 22 '16 at 23:12