Group by Minimum Value in One Field While Selecting Distinct Rows

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.

Group by column and select all fields in rows with multiple minimum values

Rank your rows with ROW_NUMBER:

select id, game, points
from
(
  select
    g.*,
    row_number() over (partition by game
                       order by points, id) as rn
  from games g
) ranked
where rn = 1
order by game;

This requires MySQL 8.

Another solution: select all rows for which not exists a better row.

select *
from games g
where not exist
(
  select null
  from games g2
  where g2.game = g.game
  and
  (
    g2.points < g.points
    or
    (g2.points = g.points and g2.id < g.id)
  )
)
order by game;

SQL query to select distinct row with minimum value

Use:

SELECT tbl.*
FROM TableName tbl
  INNER JOIN
  (
    SELECT Id, MIN(Point) MinPoint
    FROM TableName
    GROUP BY Id
  ) tbl1
  ON tbl1.id = tbl.id
WHERE tbl1.MinPoint = tbl.Point

Select DISTINCT for some columns, and the minimum value for others (What is `Group by`?)

SELECT MyName, MIN(MyDateTime)
FROM TableA
GROUP BY MyName;

get id of min value, grouped by another column

Think of this as filtering, not aggregation. I would do:

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

This has the advantage that it can make use of an index on (date, price).

If there are duplicate minimum prices on a given date, this will retrieve multiple rows.

One way to handle duplicates is to return them as a list:

select t.date, min(t.price), group_concat(t.id) as ids
from t
where t.price = (select min(t2.price)
                 from t t2
                 where t2.date = t.date
                )
group by date;

SQL - How to use GROUP BY to select 1 row for each distinct value

As suggested by JustinStolle, he was correct in his suggestion for aggregate functions and a GROUP BY:

SELECT 
(
SELECT count(distinct cases.casenum)
FROM (cases INNER JOIN user_case_data ON cases.casenum=user_case_data.casenum)
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
)
AS "Total Lost Files", cases.casenum AS "Case Number", user_case_data.discharged_date AS "Discharged Date",

MAX(case when case_notes.topic like 'LOS Case Status Update' THEN case_notes.note_date else null end)
AS GENDOC_31_Mailed,

MAX(case when case_checklist.code='101' then case_checklist.due_date else null end)
AS ADVISED_ATTORNEY,

MAX(case when case_notes.topic like 'LOS Updated Lein Ltr' THEN case_notes.note_date else null end)
AS "Sent Updated Lien Ltr",

MAX(case when case_checklist.code='109' then case_checklist.due_date else null end)
AS "Time Allocation Completed",

MAX(case when case_checklist.code='110' then case_checklist.due_date else null end)
AS "Attorney Signed Affidavit",

MAX(case when case_checklist.code='111' then case_checklist.due_date else null end)
AS "Lien Letters Sent",

MAX(case when case_checklist.code='112' then case_checklist.due_date else null end)
AS "Sent Lien to Counsel",

MAX(case when case_checklist.code='113' then case_checklist.due_date else null end)
AS "Received Costs and Transferred"

FROM cases 
LEFT JOIN case_checklist ON cases.casenum = case_checklist.case_id
LEFT JOIN user_case_data ON case_checklist.case_id=user_case_data.casenum
LEFT JOIN case_notes ON user_case_data.casenum=case_notes.case_num AND case_notes.topic LIKE 'LOS Case Status Update'
WHERE (user_case_data.discharged_date >= '##START##'
AND user_case_data.discharged_date <= '##END##')
GROUP BY cases.casenum, user_case_data.discharged_date
ORDER BY user_case_data.discharged_date ASC;

Select columns by min value - group by

It works in some RDBMSs like MySQL. MySQL docs says:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

But in case of SQL Server, you need to include all fields in the GROUP BY clause. Otherwise, it will report an error like:

Column 'ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

See MSDN.

---

Related Topics