CODEWITHSUNDEEP
mysql
Samantha
Samantha

Reputation: 321

MySQL date subquery and unique across two rows

Here's my current query.

SELECT  *

FROM    covered_panel_table 

WHERE   panel_id = "2" AND
        date_added <= "2019-03-22" 

ORDER BY    gene ASC, genes_covered_by_panel_id ASC

I want to add to the query if there are multiple entries with the sample panel_id, gene, and exon choose the one which is less than or equal to the report date or if both are greater choose the newest entry.

describe covered_panel_table;

+---------------------------+-------------+------+-----+
| Field                     | Type        | Null | Key |
+---------------------------+-------------+------+-----+
| covered_panel_id          | int(11)     | NO   | PRI | 
| panel_id                  | int(11)     | YES  |     |
| gene                      | varchar(7)  | YES  |     | 
| exon                      | varchar(17) | YES  |     | 
| accession_num             | varchar(16) | YES  |     |
| date_added                | date        | YES  |     | 
+---------------------------+-------------+------+-----+

UPDATED:

Example data: 

+------------------+----------+--------+-----------------+----------------+------------+
| covered_panel_id | panel_id | gene   | exon            | accession_num  | date_added |
+------------------+----------+--------+-----------------+----------------+------------+
|                2 |        2 | ASXL1  | 12              | NM_015338.5    | 2017-09-22 |
|              245 |        2 | BCOR   | all             | NM_001123385.1 | 2019-03-22 |
|                3 |        2 | BCOR   | all             | NM_017745.5    | 2017-09-22 |
|                4 |        2 | BRAF   | 15              | NM_004333.4    | 2017-09-22 |
|                7 |        2 | CBL    | 8, 9            | NM_005188.3    | 2019-03-18 |
|                6 |        2 | DNMT3a | all             | NM_022552.4    | 2019-03-25 |
+------------------+----------+--------+-----------------+----------------+------------+

Expected Result

+------------------+----------+--------+-----------------+----------------+------------+
| covered_panel_id | panel_id | gene   | exon            | accession_num  | date_added |
+------------------+----------+--------+-----------------+----------------+------------+
|                2 |        2 | ASXL1  | 12              | NM_015338.5    | 2017-09-22 |
|              245 |        2 | BCOR   | all             | NM_001123385.1 | 2019-03-22 |
|                4 |        2 | BRAF   | 15              | NM_004333.4    | 2017-09-22 |
|                7 |        2 | CBL    | 8, 9            | NM_005188.3    | 2019-03-18 |
+------------------+----------+--------+-----------------+----------------+------------+

Upvotes: 0

Views: 33

Answers (1)

Alex
Alex

Reputation: 17289

https://www.db-fiddle.com/f/uiGXVQ85yYez4YdEF12Mt5/0

SELECT  cpt.*

FROM    covered_panel_table cpt 
LEFT JOIN covered_panel_table cpt1
  ON cpt.panel_id = cpt1.panel_id
     AND cpt.gene = cpt1.gene
     AND cpt.exon = cpt1.exon
     AND cpt.date_added < cpt1.date_added
WHERE   cpt.panel_id = "2" AND
        cpt.date_added <= "2019-03-22" 
        AND cpt1.covered_panel_id IS NULL

ORDER BY    cpt.gene ASC

I've removed genes_covered_by_panel_id ASC form ORDER BY since that column is not defined for the table.

Upvotes: 3

Related Questions