Extra Fields with SQL Min() & Group By

Extra Fields with SQL MIN() & GROUP BY

If you wanted to get the "cheapest" employee in each department you would have two choices off the top of my head:

SELECT
     E.*     -- Don't actually use *, list out all of your columns
FROM
     Employees E
INNER JOIN
     (
          SELECT
               department,
               MIN(salary) AS min_salary
          FROM
               Employees
          GROUP BY
               department
     ) AS SQ ON
     SQ.department = E.department AND
     SQ.min_salary = E.salary

Or you can use:

SELECT
     E.*
FROM
     Employees E1
LEFT OUTER JOIN Employees E2 ON
     E2.department = E1.department AND
     E2.salary < E1.salary
WHERE
     E2.employee_id IS NULL -- You can use any NOT NULL column here

The second statement works by effectively saying, show me all employees where you can't find another employee in the same department with a lower salary.

In both cases, if two or more employees have equal salaries that are the minimum you will get them both (all).

SQL MIN() with GROUP BY select additional columns

One method uses a correlated subquery:

select t.*
from t
where t.price = (select min(t2.price) from t t2 where t2.productid = t.productid);

In most databases, this has very good performance with an index on (productid, price).

Select additional fields when using GROUP BY

If you want to select rows from Driver along with the max() and avg() values for speed, then use window functions:

SELECT d.*, CAST(d.date as DATE) as date,
       MAX(d.speed) OVER (PARTITION BY CAST(d.date as DATE)) AS maxSpeed,
       AVG(d.speed) OVER (PARTITION BY CAST(d.date as date)) AS avgSpeed
FROM Driver as d
WHERE d.date between '2014-01-01 09:00:00.491' and '2014-03-11 17:00:00.491'
ORDER BY date DESC;

EDIT:

If you want one row per driver, per day, then use a simple group by:

SELECT d.userId, CAST(d.date as DATE) as date,
       MAX(d.speed) as maxSpeed, AVG(d.speed) as avgSpeed
FROM Driver as d
WHERE d.date between '2014-01-01 09:00:00.491' and '2014-03-11 17:00:00.491'
GROUP BY d.userId, CAST(d.date as DATE) 
ORDER BY date DESC;

If you want to add additional columns, you need to add them both to the select and the group by.

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.

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 GROUP BY and MIN to find people with minimum salary in each department

Instead fo a IN clause you can also use an inner join

SELECT first_name, last_name, salary, department_id  
FROM employees  
INNER JOIN ( SELECT department_id, MIN(salary)  min_sal
FROM employees  
GROUP BY department_id 
) t on t.department_id =employees.department_id  
    and employees.salary = t.min_sal;

this should be better for performance

MySQL GROUP BY with MIN - incorrect column data

In general, unless you're trying to perform some sort of MySQL "magic" you should always group by every non-aggregate, non-constant column in your SELECT list.

In your case, the best approach is to get a list of (name, # bedrooms, minimum rent), and then find all the rows that match these values - in other words, all rows whose (name, # bedrooms, rent) match the list with the minimum rent:

SELECT ru.id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)

This query will give all lowest-rent units by name/bedrooms. The sample data has ties for lowest in a couple of places. To include only one of the "tied" rows (the one with the lowest rental_units.id, try this instead - the only change is the MIN(ru.id) on the first line and the addition of an overall GROUP BY on the last line:

SELECT MIN(ru.id) AS ru_id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)
GROUP BY run.name, ru.rent, ru.bedrooms

---

Related Topics