SQL value of one column based on max values in other selected rows

select row with max value in one column
mysql select row with max value group by
sql server select row with max value
sql only select row with max value
postgres select row with max value
sql select max value from multiple columns
mysql select multiple max values
sql select row with max date

I am using the Northwind sample database table and I would like to find the top categoryId for each supplierId...

+-----------+----------------------------------+------------+------------+
| ProductID | ProductName                      | SupplierID | CategoryID |
+-----------+----------------------------------+------------+------------+
| 1         | Chai                             | 1          | 1          |
+-----------+----------------------------------+------------+------------+
| 2         | Chang                            | 1          | 1          |
+-----------+----------------------------------+------------+------------+
| 3         | Aniseed Syrup                    | 1          | 2          |
+-----------+----------------------------------+------------+------------+
| 4         | Chef Anton's Cajun Seasoning     | 2          | 2          |
+-----------+----------------------------------+------------+------------+
| 5         | Chef Anton's Gumbo Mix           | 2          | 2          |
+-----------+----------------------------------+------------+------------+
| 6         | Grandma's Boysenberry Spread     | 3          | 2          |
+-----------+----------------------------------+------------+------------+
| 7         | Uncle Bob's Organic Dried Pears  | 3          | 7          |
+-----------+----------------------------------+------------+------------+
| 8         | Northwoods Cranberry Sauce       | 3          | 2          |
+-----------+----------------------------------+------------+------------+
| 9         | Mishi Kobe Niku                  | 4          | 6          |
+-----------+----------------------------------+------------+------------+
| 10        | Ikura                            | 4          | 8          |
+-----------+----------------------------------+------------+------------+
| 11        | Queso Cabrales                   | 5          | 4          |
+-----------+----------------------------------+------------+------------+
| 12        | Queso Manchego La Pastora        | 5          | 4          |
+-----------+----------------------------------+------------+------------+
| 13        | Konbu                            | 6          | 8          |
+-----------+----------------------------------+------------+------------+
| 14        | Tofu                             | 6          | 7          |
+-----------+----------------------------------+------------+------------+
| 15        | Genen Shouyu                     | 6          | 2          |
+-----------+----------------------------------+------------+------------+
| 16        | Pavlova                          | 7          | 3          |
+-----------+----------------------------------+------------+------------+
| 17        | Alice Mutton                     | 7          | 6          |
+-----------+----------------------------------+------------+------------+
| 18        | Carnarvon Tigers                 | 7          | 8          |
+-----------+----------------------------------+------------+------------+
| 19        | Teatime Chocolate Biscuits       | 8          | 3          |
+-----------+----------------------------------+------------+------------+
| 20        | Sir Rodney's Marmalade           | 8          | 3          |
+-----------+----------------------------------+------------+------------+
| 21        | Sir Rodney's Scones              | 8          | 3          |
+-----------+----------------------------------+------------+------------+
| 22        | Gustaf's Knäckebröd              | 9          | 5          |
+-----------+----------------------------------+------------+------------+
| 23        | Tunnbröd                         | 9          | 5          |
+-----------+----------------------------------+------------+------------+
| 24        | Guaraná Fantástica               | 10         | 1          |
+-----------+----------------------------------+------------+------------+
| 25        | NuNuCa Nuß-Nougat-Creme          | 11         | 3          |
+-----------+----------------------------------+------------+------------+
| 26        | Gumbär Gummibärchen              | 11         | 3          |
+-----------+----------------------------------+------------+------------+
| 27        | Schoggi Schokolade               | 11         | 3          |
+-----------+----------------------------------+------------+------------+
| 28        | Rössle Sauerkraut                | 12         | 7          |
+-----------+----------------------------------+------------+------------+
| 29        | Thüringer Rostbratwurst          | 12         | 6          |
+-----------+----------------------------------+------------+------------+
| 30        | Nord-Ost Matjeshering            | 13         | 8          |
+-----------+----------------------------------+------------+------------+
| 31        | Gorgonzola Telino                | 14         | 4          |
+-----------+----------------------------------+------------+------------+
| 32        | Mascarpone Fabioli               | 14         | 4          |
+-----------+----------------------------------+------------+------------+
| 33        | Geitost                          | 15         | 4          |
+-----------+----------------------------------+------------+------------+
| 34        | Sasquatch Ale                    | 16         | 1          |
+-----------+----------------------------------+------------+------------+
| 35        | Steeleye Stout                   | 16         | 1          |
+-----------+----------------------------------+------------+------------+
| 36        | Inlagd Sill                      | 17         | 8          |
+-----------+----------------------------------+------------+------------+
| 37        | Gravad lax                       | 17         | 8          |
+-----------+----------------------------------+------------+------------+
| 38        | Côte de Blaye                    | 18         | 1          |
+-----------+----------------------------------+------------+------------+
| 39        | Chartreuse verte                 | 18         | 1          |
+-----------+----------------------------------+------------+------------+
| 40        | Boston Crab Meat                 | 19         | 8          |
+-----------+----------------------------------+------------+------------+
| 41        | Jack's New England Clam Chowder  | 19         | 8          |
+-----------+----------------------------------+------------+------------+
| 42        | Singaporean Hokkien Fried Mee    | 20         | 5          |
+-----------+----------------------------------+------------+------------+
| 43        | Ipoh Coffee                      | 20         | 1          |
+-----------+----------------------------------+------------+------------+
| 44        | Gula Malacca                     | 20         | 2          |
+-----------+----------------------------------+------------+------------+
| 45        | Rogede sild                      | 21         | 8          |
+-----------+----------------------------------+------------+------------+
| 46        | Spegesild                        | 21         | 8          |
+-----------+----------------------------------+------------+------------+
| 47        | Zaanse koeken                    | 22         | 3          |
+-----------+----------------------------------+------------+------------+
| 48        | Chocolade                        | 22         | 3          |
+-----------+----------------------------------+------------+------------+
| 49        | Maxilaku                         | 23         | 3          |
+-----------+----------------------------------+------------+------------+
| 50        | Valkoinen suklaa                 | 23         | 3          |
+-----------+----------------------------------+------------+------------+
| 51        | Manjimup Dried Apples            | 24         | 7          |
+-----------+----------------------------------+------------+------------+
| 52        | Filo Mix                         | 24         | 5          |
+-----------+----------------------------------+------------+------------+
| 53        | Perth Pasties                    | 24         | 6          |
+-----------+----------------------------------+------------+------------+
| 54        | Tourtière                        | 25         | 6          |
+-----------+----------------------------------+------------+------------+
| 55        | Pâté chinois                     | 25         | 6          |
+-----------+----------------------------------+------------+------------+
| 56        | Gnocchi di nonna Alice           | 26         | 5          |
+-----------+----------------------------------+------------+------------+
| 57        | Ravioli Angelo                   | 26         | 5          |
+-----------+----------------------------------+------------+------------+
| 58        | Escargots de Bourgogne           | 27         | 8          |
+-----------+----------------------------------+------------+------------+
| 59        | Raclette Courdavault             | 28         | 4          |
+-----------+----------------------------------+------------+------------+
| 60        | Camembert Pierrot                | 28         | 4          |
+-----------+----------------------------------+------------+------------+
| 61        | Sirop d'érable                   | 29         | 2          |
+-----------+----------------------------------+------------+------------+
| 62        | Tarte au sucre                   | 29         | 3          |
+-----------+----------------------------------+------------+------------+
| 63        | Vegie-spread                     | 7          | 2          |
+-----------+----------------------------------+------------+------------+
| 64        | Wimmers gute Semmelknödel        | 12         | 5          |
+-----------+----------------------------------+------------+------------+
| 65        | Louisiana Fiery Hot Pepper Sauce | 2          | 2          |
+-----------+----------------------------------+------------+------------+
| 66        | Louisiana Hot Spiced Okra        | 2          | 2          |
+-----------+----------------------------------+------------+------------+
| 67        | Laughing Lumberjack Lager        | 16         | 1          |
+-----------+----------------------------------+------------+------------+
| 68        | Scottish Longbreads              | 8          | 3          |
+-----------+----------------------------------+------------+------------+
| 69        | Gudbrandsdalsost                 | 15         | 4          |
+-----------+----------------------------------+------------+------------+
| 70        | Outback Lager                    | 7          | 1          |
+-----------+----------------------------------+------------+------------+
| 71        | Flotemysost                      | 15         | 4          |
+-----------+----------------------------------+------------+------------+
| 72        | Mozzarella di Giovanni           | 14         | 4          |
+-----------+----------------------------------+------------+------------+
| 73        | Röd Kaviar                       | 17         | 8          |
+-----------+----------------------------------+------------+------------+
| 74        | Longlife Tofu                    | 4          | 7          |
+-----------+----------------------------------+------------+------------+
| 75        | Rhönbräu Klosterbier             | 12         | 1          |
+-----------+----------------------------------+------------+------------+
| 76        | Lakkalikööri                     | 23         | 1          |
+-----------+----------------------------------+------------+------------+
| 77        | Original Frankfurter grüne Soße  | 12         | 2          |
+-----------+----------------------------------+------------+------------+

Using the query

SELECT SupplierID, CategoryID, COUNT(CategoryID) AS Total FROM [dbo].[Products] GROUP BY CategoryID, SupplierID

I get the table

+------------+------------+-------+
| SupplierID | CategoryID | Total |
+------------+------------+-------+
| 1          | 1          | 2     |
+------------+------------+-------+
| 1          | 2          | 1     |
+------------+------------+-------+
| 2          | 2          | 4     |
+------------+------------+-------+
| 3          | 2          | 2     |
+------------+------------+-------+
| 3          | 7          | 1     |
+------------+------------+-------+
| 4          | 6          | 1     |
+------------+------------+-------+
| 4          | 7          | 1     |
+------------+------------+-------+
| 4          | 8          | 1     |
+------------+------------+-------+
| 5          | 4          | 2     |
+------------+------------+-------+
| 6          | 2          | 1     |
+------------+------------+-------+
| 6          | 7          | 1     |
+------------+------------+-------+
| 6          | 8          | 1     |
+------------+------------+-------+
| 7          | 1          | 1     |
+------------+------------+-------+
| 7          | 2          | 1     |
+------------+------------+-------+
| 7          | 3          | 1     |
+------------+------------+-------+
| 7          | 6          | 1     |
+------------+------------+-------+
| 7          | 8          | 1     |
+------------+------------+-------+
| 8          | 3          | 4     |
+------------+------------+-------+
| 9          | 5          | 2     |
+------------+------------+-------+
| 10         | 1          | 1     |
+------------+------------+-------+
| 11         | 3          | 3     |
+------------+------------+-------+
| 12         | 1          | 1     |
+------------+------------+-------+
| 12         | 2          | 1     |
+------------+------------+-------+
| 12         | 5          | 1     |
+------------+------------+-------+
| 12         | 6          | 1     |
+------------+------------+-------+
| 12         | 7          | 1     |
+------------+------------+-------+
| 13         | 8          | 1     |
+------------+------------+-------+
| 14         | 4          | 3     |
+------------+------------+-------+
| 15         | 4          | 3     |
+------------+------------+-------+
| 16         | 1          | 3     |
+------------+------------+-------+
| 17         | 8          | 3     |
+------------+------------+-------+
| 18         | 1          | 2     |
+------------+------------+-------+
| 19         | 8          | 2     |
+------------+------------+-------+
| 20         | 1          | 1     |
+------------+------------+-------+
| 20         | 2          | 1     |
+------------+------------+-------+
| 20         | 5          | 1     |
+------------+------------+-------+
| 21         | 8          | 2     |
+------------+------------+-------+
| 22         | 3          | 2     |
+------------+------------+-------+
| 23         | 1          | 1     |
+------------+------------+-------+
| 23         | 3          | 2     |
+------------+------------+-------+
| 24         | 5          | 1     |
+------------+------------+-------+
| 24         | 6          | 1     |
+------------+------------+-------+
| 24         | 7          | 1     |
+------------+------------+-------+
| 25         | 6          | 2     |
+------------+------------+-------+
| 26         | 5          | 2     |
+------------+------------+-------+
| 27         | 8          | 1     |
+------------+------------+-------+
| 28         | 4          | 2     |
+------------+------------+-------+
| 29         | 2          | 1     |
+------------+------------+-------+
| 29         | 3          | 1     |
+------------+------------+-------+

As you can see supplier 1 makes 2 category 1 products and 1 catergory 2 product. Therefore the first line in the query should read

+------------+------------+-------+
| SupplierID | CategoryID | Total |
+------------+------------+-------+
| 1          | 1          | 2     |
+------------+------------+-------+

Next should be supplierId #2 which makes a total of 4 category 2 products. The final table should look like this...

+------------+------------+-------+
| SupplierID | CategoryID | Total |
+------------+------------+-------+
| 1          | 1          | 2     |
+------------+------------+-------+
| 2          | 2          | 4     |
+------------+------------+-------+
| 3          | 2          | 2     |
+------------+------------+-------+
| 4          | 6          | 1     |
+------------+------------+-------+
| 5          | 4          | 2     |
+------------+------------+-------+
| 6          | 2          | 1     |
+------------+------------+-------+
| 7          | 1          | 1     |
+------------+------------+-------+
| 8          | 3          | 4     |
+------------+------------+-------+
| 9          | 5          | 2     |
+------------+------------+-------+
| 11         | 3          | 3     |
+------------+------------+-------+
| 12         | 1          | 1     |
+------------+------------+-------+
| 13         | 8          | 1     |
+------------+------------+-------+
| 14         | 4          | 3     |
+------------+------------+-------+
| 15         | 4          | 3     |
+------------+------------+-------+
| 16         | 1          | 3     |
+------------+------------+-------+
| 17         | 8          | 3     |
+------------+------------+-------+
| 18         | 1          | 2     |
+------------+------------+-------+
| 19         | 8          | 2     |
+------------+------------+-------+
| 20         | 1          | 1     |
+------------+------------+-------+
| 21         | 8          | 2     |
+------------+------------+-------+
| 22         | 3          | 2     |
+------------+------------+-------+
| 23         | 3          | 2     |
+------------+------------+-------+
| 24         | 5          | 1     |
+------------+------------+-------+
| 25         | 6          | 2     |
+------------+------------+-------+
| 26         | 5          | 2     |
+------------+------------+-------+
| 27         | 8          | 1     |
+------------+------------+-------+
| 28         | 4          | 2     |
+------------+------------+-------+
| 29         | 2          | 1     |
+------------+------------+-------+
| 29         | 3          | 1     |
+------------+------------+-------+ 

I know a lot of suppliers only make one item for a given category and this isn't a great example but just trying to learn here.

Thanks

I think you can make use of row number based on partition by supplier and then use aggregate function along with row number for ranking. Then only select the one where you have more rows for a given supplier. I just took some part of your sample data and did it in this way.

with cte as (

 select  1 as ProductID, 'Chai'                            as ProductNmae,  1 as SupplierID,  1   as CategoryID   union all                               
 select  2 as ProductID, 'Chang'                           as ProductNmae, 1  as SupplierID, 1    as CategoryID   union all                               
 select  3 as ProductID, 'Aniseed Syrup'                   as ProductNmae, 1  as SupplierID, 2    as CategoryID   union all                               
 select  4 as ProductID, 'Chef Anton''s Cajun Seasoning'   as ProductNmae,  2 as SupplierID,  2   as CategoryID   union all                               
 select  5 as ProductID, 'Chef Anton''s Gumbo Mix'          as ProductNmae, 2  as SupplierID, 2    as CategoryID union all  
 select  6  as ProductID,  'Grandma''s Boysenberry Spread'     as Product_name , 3  as SupplierID,  2  as CategoryID union all         
 select   7 as ProductID,   'Uncle Bob''s Organic Dried Pears' as Product_name ,  3 as SupplierID,  7  as CategoryID union all         
 select   8 as ProductID,   'Northwoods Cranberry Sauce'       as Product_name ,  3 as SupplierID,  2  as CategoryID     )
 select t.SupplierID, t.CategoryID, t.total from ( 
 select supplierID, CategoryID , ROW_NUMBER() over (partition by supplierID order by count(1) desc) rownum, count(1) total    from cte     
 group by supplierID, CategoryID  ) t
 where t.rownum = 1 

Output:

SupplierID  CategoryID  total
1               1        2
2               2        2
3               2        2

Selecting the row having the max value for one field, grouping by , I have a table updates with various fields - for instance matchnum, time, entrant1, votes1. Values in the table can look like: matchnum time entrant1 votes1 Selecting the row having the max value for one field, grouping by another field mysql goes ahead and tries to execute invalid sql anyway, which,� Select Rows with Maximum Value on a Column Example 2. In this example, we will show how to select rows with max value along with remaining columns. It is useful if you want to return the remaining columns (non-group by columns). For this SQL Server example, we used the Inner Join to join the employee table with itself.

In Sql server you can write a query as:

select SupplierID , 
        CategoryID ,
        Total 
from (
select 
        SupplierID , 
        CategoryID ,
        Total ,
        ROW_NUMBER() over (partition by SupplierID order by  Total desc) as rownum
from (
        SELECT SupplierID
            , CategoryID
            , COUNT(CategoryID) AS Total 
        FROM [dbo].[Products]
        GROUP BY CategoryID, SupplierID
        ) as Innertable

) as Outertable
where rownum = 1
order by SupplierID

MySQL, SELECT rows with MAX(Column value), DISTINCT by another column in MySQL ? TablePlus is a modern, native tool with an elegant UI that allows you to as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. Notice in the data above that for 2014 data there is one min value (100) and one max (1200), but for 2015 there are two min values (100) and two max values (1200). I can do a GROUP BY query like below to find the min and max values, but this won't allow me to get the other column values that I need.

First you have to generate the category counts by supplier, then you have to rank them from highest to lowest, and finally select only the highest. In the following query, I've done that by using nested queries:

-- Select only the top category counts by supplier
SELECT
    [SupplierID],
    [CategoryID],
    [Total]
FROM (
    -- Rank category counts by supplier
    SELECT
        *,
        RANK() OVER (PARTITION BY [SupplierID] ORDER BY [Total] DESC) AS [Rank]
    FROM (
        -- Generate category counts by supplier
        SELECT
            [SupplierID],
            [CategoryID],
            COUNT(*) AS [Total]
        FROM [Products]
        GROUP BY
            [SupplierID],
            [CategoryID]
    ) AS SupplierCategoryCounts
) AS RankedSupplierCategoryCounts
WHERE [Rank] = 1
ORDER BY [SupplierID]

How can I SELECT rows with MAX(Column value), DISTINCT by , Select BOTH the home and its max date time, then join back to the top table on BOTH the fields: Want to learn SQL from basics! Here's the right� No problem :) If you want the exact output as shown in your example you can use the following SQL query to create a Power Pivot table: WITH CTE_MaxRev AS ( SELECT ID, MAX(Rev) OVER (PARTITION BY ID) AS MaxRev FROM #Table AS t2 ) SELECT ID, Rev, [Duration Time] FROM #Table AS t1 WHERE EXISTS ( SELECT ID, MaxRev FROM CTE_MaxRev AS mr WHERE t1.ID = mr.ID AND t1.Rev = mr.MaxRev )

How to select a row based on the maximum value of a column, when , select subscriber, date_column, time_column, value from ( select subscriber, date_column, time_column, value, rank() over (partition by subscriber order by� Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause: SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL; SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;

SQL: MAX Function, The SQL MAX function is used to return the maximum value of an expression Because you have listed one column in your SQL SELECT statement that is the select list and to the group by clause returns multiple lines for each report; This syntax is optimized for Oracle and may not work for other database technologies. In other words: first, I want to select distinct e-mail addresses, and then return rows containing distinct e-mail addresses. Note: Just using the "Distinct" keyword will not work here, as it will select distinct rows. My requirement is to select distinct email addresses, and then to select rows containing those addresses.

SQL MAX() with HAVING, WHERE, IN, MAX function to find the maximum value of a column over each group and how SQL The SQL IN OPERATOR which checks a value within a set of values and retrieve the rows from the table can also be used with MAX function. SELECT cust_city, cust_country, MAX(outstanding_amt) FROM customer� If there can be ties (rows that have the same id and round) you may want to use the row_number analytic function instead of rank-- that will arbitrarily pick one of the two tied rows to have a rnk of 1 rather than returning both as rank would. If you wanted to use the MAX analytic function, you could also do something like

Comments
  • Northwind strongly suggests SQL Server, so I removed the MySQL tag.
  • Note that RANK() will select more than one row if there are multiple categories with the same count per supplier. If you only want one row per supplier, use ROW_NUMBER(), preferably with a further ORDER BY column to ensure consistent return values.
  • I'm afraid this code gives the same results I am currently getting. It shows the count for each SupplierID and CategoryID pair but not the CategoryID with the most products per SupplierID. The expected output should only be 29 rows.
  • @GuyFawkes apologies, there was a small bug in the code, please try it now kepping in mind my note about RANK vs ROW_NUMBER