0

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.

Desired rows

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?

5
  • 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
    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. Mar 25, 2016 at 19:06
  • @Avarkx, You are correct and I want the shelf with value 1. Mar 25, 2016 at 19:12
  • 1
    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
    Mar 25, 2016 at 19:15
  • What's your sqlmode? Mar 25, 2016 at 19:29

1 Answer 1

1

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
1
  • 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. Mar 25, 2016 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.