SQL select a specific row based on key when performing a select distinct / group by

88
October 27, 2018, at 1:10 PM

I have a dataset that resembles the following:

|   ID    |   Match1    |   Match2    |    Key     |     Val1   |
|   1     |     a       |    b        |     a      |      a     | 
|   2     |     a       |    b        |     c      |      b     |
|   3     |     a       |    b        |    keep    |      c     |
|   4     |     a       |    a        |     a      |      d     |

You'll see rows 1-3 have matching values in Match1 and Match2, and row 3 has "keep" in the Key column.

I'm trying to write a query that will drop rows 1 and 2 based on matching criteria, but keep row 3 because of the key provided. The resulting dataset would look as follows:

|   ID    |   Match1    |   Match2    |    Key     |     Val1   |
|   3     |     a       |    b        |    keep    |      c     |
|   4     |     a       |    a        |     a      |      d     |

Any suggestions for how to make this work?

Answer 1

Let me guess that you want one row per match1/match2 combo, with preference for the "keep". If so:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.match1 = t.match1 and t2.match2 = t.match2
              order by (key = 'keep') desc,  -- highest preference for "keep"
                       id desc               -- most recent id
              limit 1
             );

To handle NULLs you can use a NULL-safe comparison:

select t.*
from t
where t.id = (select t2.id
              from t t2
              where t2.match1 <=> t.match1 and t2.match2 <= >t.match2
              order by (key = 'keep') desc,  -- highest preference for "keep"
                       id desc               -- most recent id
              limit 1
             );
Rent Charter Buses Company
READ ALSO
Does InnoDB lock the whole table for a delete which uses part of a composed key?

Does InnoDB lock the whole table for a delete which uses part of a composed key?

I have a MySQL table (call it 'my_table') with a composed primary key with 4 columns (call them 'a', 'b', 'c' and 'd')

123
media_handle_upload function produces blank page

media_handle_upload function produces blank page

So currently I am using a customized form that allows logged-in users to upload imagesI am using the Wordpress media_handle_upload function to handle this task

168
Distinct based on one row with multiple non-aggregate columns selected

Distinct based on one row with multiple non-aggregate columns selected

I'm trying to run a query that returns distinct AddressIDs

127
how do i print data in php

how do i print data in php

hi this is my code but its run without any results, i have a table "sliders " that contains id and url for image and i'm sure connectphp in fine

105