How to Group and Choose Lowest Value in SQL

How to GROUP and choose lowest value in SQL

select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to

SQL: How to find min value per group in sql?

As mentioned you should use concatenation to create a single date and then select the lowest value.

select domain, MIN(CAST(CONCAT(`year`, '-'`,month`,'-',`day`) AS DATE)) from snapshots group by domain

Haven't tested this but this should give you an idea.

sql - select single ID for each group with the lowest value

Use row_number():

select t.*
from (select t.*,
             row_number() over (partition by groupid order by rank) as seqnum
      from t
     ) t
where seqnum = 1;

Group by minimum value in one field while selecting distinct rows

How about something like:

SELECT mt.*     
FROM MyTable mt INNER JOIN
    (
        SELECT id, MIN(record_date) AS MinDate
        FROM MyTable
        GROUP BY id
    ) t ON mt.id = t.id AND mt.record_date = t.MinDate

This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.

Select Rows with min value for each group

But the best would be save dates as date column. The you can use all function for dates

CREATE TABLE table1 (
  [Date]  varchar(10),
  [Container ID] INTEGER
);

INSERT INTO table1
  ([Date], [Container ID])
VALUES
  ('1/1', '1'),
  ('2/2', '1'),
  ('3/3', '1'),
  ('4/4', '2'),
  ('5/5', '2'),
  ('6/6', '3'),
  ('7/7', '3');
GO

SELECT MIN([Date]), [Container ID] FROM table1 GROUP BY [Container ID]
GO

(No column name) | Container ID
:--------------- | -----------:
1/1              |            1
4/4              |            2
6/6              |            3

db<>fiddle here

MySQL - Select group by with minimum value

One standard way of dealing with this in MySQL, which doesn't have wide support for analytic functions, is to use a subquery which identifies the minimum val2 values for each descr group. Then use this subquery to restrict the records of test to what you want appearing in your result set.

SELECT t1.*
FROM test t1
INNER JOIN
(
    SELECT descr, MIN(val2) AS min_val2
    FROM test
    GROUP BY descr
) t2
    ON t1.descr = t2.descr AND
       t1.val2  = t2.min_val2

Demo here:

SQLFiddle

Mark the record with the lowest value in a group in SQL

The window functions should to the trick . Use dense_rank() if you want to see ties.

Select *
      ,R = case when row_number() over (partition by ID1,Name order by ID2) = 1
                then 'Y'
                else '' 
           end
 From  YourTable

---

I should add... The window functions can be invaluable. They are well worth your time experimenting with them.

MySQL - Fetching lowest value

Solution1:

SELECT t1.* FROM your_table t1
JOIN (
  SELECT MIN(value) AS min_value, dealer
  FROM your_table 
  GROUP BY dealer
) AS t2 ON t1.dealer = t2.dealer AND t1.value = t2.min_value

Solution2 (recommended, much faster than solution1):

SELECT t1.* FROM your_table t1
LEFT JOIN your_table t2
ON t1.dealer = t2.dealer AND t1.value > t2.value
WHERE t2.value IS NULL

This problem is very famous, so there is a special page for this in Mysql's manual.

Check this: Rows Holding the Group-wise Maximum/Minimum of a Certain Column

---

Related Topics