MySQL – Remove Duplicate Rows from SELECT Query Using GROUP BY

group byMySQL

I have a situation to grab rows with a minimum value of a certain column. Below is the image of my made up table to explain what I am trying to do.

I need to pull one record per instance_oid with the minimum shelf number. I wrote the query like this:

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site, bldg, room, shelf

This gives same record in multilpe rows (as many rows as I have for each instance_oid). How do I make sure I get only one record, i.e, the one with the min(shelf) as I highlighted in the attached image?

Best Answer

take off shelf at the end of your group by

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site,  bldg, room

Note: As mentioned in a comment the sites are different (and not highlighted in yellow), but I'm assuming you want newly grouped row output when that happens...

if not I would not include the site data:

select instance_oid, bldg, room, min(shelf) group by instance_oid, bldg, room

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.

MySQL – Query Duration Increases 10x After Adding One More Join

You have forgotten the join criteria and ist.id = i_team_2.status_id.

If you had used the modern JOIN syntax this would have stood out like a sore thumb.

Best Answer

Related Solutions

Mysql – remove duplicate rows in thesql table that does not contain primary key

MySQL – Query Duration Increases 10x After Adding One More Join

Related Question