How to Get Only One Row Against Each Id in MySQL

How can I select only one row for each ID in MySQL?

Take a ganders at SELECT DISTINCT

Select Must Return only one row against every id

You can use row number in a sub query to give you an ordering and then just pick the first one.

SELECT *
FROM (
  SELECT id, date, sales_amount,
         ROW_NUMBER() OVER (ORDER BY date DESC) as RN
  FROM   customer
  WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
       AND id = 1001
) sub
WHERE RN = 1

Note if you want to do it for all customers then this is the query

SELECT *
FROM (
  SELECT id, date, sales_amount,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) as RN
  FROM   customer
  WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
) sub
WHERE RN = 1

That will give you the most recent row for each customer.

How to select the first row for each group in MySQL?

When I write

SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;

It works. IIRC in other RDBMS such statement is impossible, because a column that doesn't belongs to the grouping key is being referenced without any sort of aggregation.

This "quirk" behaves very closely to what I want. So I used it to get the result I wanted:

SELECT * FROM 
(
 SELECT * FROM `table`
 ORDER BY AnotherColumn
) t1
GROUP BY SomeColumn
;

MySQL Select one row per ID according to highest submitted date per ID

You can find max date for each id in subquery and join it with the original table to get all the rows with all the columns (assuming there are more columns apart from id and date_submitted) like this:

select t.*
from your_table t
inner join (
    select id, max(date_submitted) date_submitted
    from your_table
    group by id
) t2 on t.id = t2.id
and t.date_submitted = t2.date_submitted;

Note that this query will return multiple rows for an id in case there are multiple rows with date_submitted equals to max date_submitted for that id. If you really want only one row per id, then the solution will be a bit different.

If you just need id and max date use:

select id, max(date_submitted) date_submitted
from your_table
group by id

mysql-select a random row from each id

You can try:

select t.*
from t
where t.color = (select t2.color
                 from t t2
                 where t2.id = t.id
                 order by rand()
                 limit 1
                );

For performance, you can try an index on (id, color).

Your code should simply not work. It uses select * with group by -- meaning that you have unaggregated columns. That should be a compile-time error.

EDIT:

LOL. Of course, the above has an issue. The subquery gets called for each row, giving each row an opportunity to be in the result set. Sigh. Sometimes code doesn't do what I want it to do. One solution is to seed the random number generator. This is "arbitrary" but not "random" -- you'll get the same values on each run:

select t.*
from t
where t.color = (select t2.color
                 from t t2
                 where t2.id = t.id
                 order by rand(concat(t2.id, t2.color))
                 limit 1
                );

If you don't have too many colors, you can use a group_concat() trick:

select t.id,
       substring_index(group_concat(color order by rand()), ',', 1)
from tA quick and dirty solution is to seed the random number generator:
group by id;

Select ONLY one row per category

Use GROUP BY :

SELECT MIN(id), cat_id, title FROM table GROUP BY cat_id

---

Related Topics