SQL MIN() While reporting Duplicates of Secondary Variable

Question

I have a dataset that looks like the following:

 ID   ORDER_MNEMONIC         Ordered_dt_tm              Given_dt_tm
 1     keta            2019-08-25 19:34:07       2019-08-25 21:30:00
 1     keta            2019-08-25 21:45:07       2019-08-25 22:21:00
 1     keta            2019-08-25 22:25:07       2019-08-25 23:30:00
 2     keta            2019-08-25 14:34:07       2019-08-25 15:30:00
 2     keta            2019-08-25 15:45:07       2019-08-25 16:21:00
 3     keta            2019-08-25 17:25:07       2019-08-25 18:30:00

I want to create an output table that contains one record per ID where that record is based on the minimum Given_dt_tm variable. For example,

 ID   ORDER_MNEMONIC          Ordered_dt_tm              Given_dt_tm
 1     keta             2019-08-25 19:34:07       2019-08-25 21:30:00
 2     keta             2019-08-25 14:34:07       2019-08-25 15:30:00
 3     keta             2019-08-25 17:25:07       2019-08-25 18:30:00

I wrote the following code but since I want to keep the Ordered_dt_tm variable in the table, the code produces a table that doesn't reflect only the min Given_dt_tm variable by patient-it is essentially a duplicate of the dataset shown above. Of note, the top row doesn't always necessarily correspond to the first (min) given_dt_tm.

Code that I wrote:

 SELECT PT_FIN, ORDER_MNEMONIC , Ordered_dt_tm , min(GIVEN_DT_TM) AS GIVEN_Time
 FROM ED_Orders_Import_Master
 WHERE ORDER_MNEMONIC = '%ketamine%' 
 Group By PT_FIN

ANSWER with slight adjustment of commented repsonse:

 SELECT *
 FROM ED_Orders_Import_Master oim
 WHERE  oim.GIVEN_DT_TM = (SELECT MIN(oim2.GIVEN_DT_TM)
                     FROM ED_Orders_Import_Master oim2
                     WHERE (oim2.PT_FIN = oim.PT_FIN) AND (ORDER_MNEMONIC like '%ketamine%')

score 1 · Answer 1 · answered Dec 03 '19 at 17:00

1

If you want the complete row with the minimum, you want filtering, not aggregation:

SELECT oim.*
FROM ED_Orders_Import_Master oim
WHERE oim.ORDER_MNEMONIC = '%ketamine%' AND
      oim.GIVEN_DT_TM = (SELECT MIN(oim2.GIVEN_DT_TM)
                         FROM ED_Orders_Import_Master oim2
                         WHERE oim2.ORDER_MNEMONIC = '%ketamine%' AND
                               oim2.PT_FIN = oim.PT_FIN
                        )

answered Dec 03 '19 at 17:00

Gordon Linoff

1,242,037
58
646
786

@gordon_linoff, thanks but this query does not produce any results – Raven Dec 03 '19 at 18:01
@larnu this is not a duplicate question, as I said in the text "Of note, the top row doesn't always necessarily correspond to the first (min) given_dt_tm" – Raven Dec 03 '19 at 18:02

SQL MIN() While reporting Duplicates of Secondary Variable

1 Answers1