Remove duplicate rows from select query using group by

Question

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?

You're grouping by shelf, so MIN( shelf ) and MAX( shelf ) will be equal to shelf. With the sample data above, you'll get two of the first oid because you're also grouping by site ( 1, 4 ). — Avarkx
– Avarkx, Commented Mar 25, 2016 at 18:58
Could you please present data in the form of SHOW CREATE TABLE My_Table\G and as INSERT INTO My_Table VALUES(.....). This makes it easier to copy and paste and try out solutions. — Vérace
– Vérace, Commented Mar 25, 2016 at 19:06
Remove site, bldg, room from both the select statement and shelf from the group by. If that's not exactly what you need, then @Vérace looks like he's willing to sort you out if you post some DDL to work with — Avarkx
– Avarkx, Commented Mar 25, 2016 at 19:15

Sting · Accepted Answer · 2016-03-25 19:20:44Z

2

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

answered Mar 25, 2016 at 19:20

Sting

2,80813 silver badges20 bronze badges

Got it, thanks! +1 and selected as the answer. I needed to do some sub queries to filter 4 from (1,4) that @Avarkx pointed out.

user3885927
– user3885927

2016-03-25 20:01:49 +00:00
Commented Mar 25, 2016 at 20:01

Add a comment |

Stack Exchange Network

Remove duplicate rows from select query using group by

1 Answer 1

Your Answer

Hot Network Questions

Remove duplicate rows from select query using group by

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions