Menu
Name Value AnotherColumn ----------- Pump 1 8000.0 Something1 Pump 1 10000.0 Something2 Pump 1 10000.0 Something3 Pump 2 3043 Something4 Pump 2 4594 Something5 Pump 2 6165 Something6 My table looks something like this. I would like to know how to select max value for each pump.
select a.name, value from out_pumptable as a, (select name, max(value) as value from out_pumptable where group by posnumber)g where and g.value = value this code does the job, but i get two entries of Pump 1 since it has two entries with same value.
280
MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).
SQL Server MAX() with GROUP BY clause example First, the GROUP BY clause divided the products into groups by the brand names. Then, the MAX() function is applied to each group to return the highest list price for each brand.
In SQL Server there are several ways to get the MIN or MAX of multiple columns including methods using UNPIVOT, UNION, CASE, etc… However, the simplest method is by using FROM … VALUES i.e. table value constructor. Let's see an example. In this example, there is a table for items with five columns for prices.
To get the maximum value from three different columns, use the GREATEST() function. Insert some records in the table using insert command. Display all records from the table using select statement.
select name, max(value) from out_pumptable group by name 
SELECT b.name, MAX(b.value) as MaxValue, MAX(b.Anothercolumn) as AnotherColumn FROM out_pumptabl INNER JOIN (SELECT name, MAX(value) as MaxValue FROM out_pumptabl GROUP BY Name) a ON a.name = b.name AND a.maxValue = b.value GROUP BY b.Name Note this would be far easier if you had a primary key. Here is an Example
SELECT * FROM out_pumptabl c WHERE PK in (SELECT MAX(PK) as MaxPK FROM out_pumptabl b INNER JOIN (SELECT name, MAX(value) as MaxValue FROM out_pumptabl GROUP BY Name) a ON a.name = b.name AND a.maxValue = b.value) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
