Vincent Prince Assah Vincent Prince Assah - 5 months ago 17
SQL Question

Return one record with multiple entries on the same date

I have a table that looks like

| OPDNo |DispensedDate | Drugname | CreatedBy|
| 011650/16 | 6/29/2016 |folic acid | admin |
| 011650/16 | 6/29/2016 |multivite | admin |
| 011650/16 | 6/21/2016 |fersolate | asah |
| 011650/16 | 6/21/2016 |amoxicyllin| eantwi |
| 025343/13 | 5/23/2016 |aspirin | emelia |


And i want the record selection to be like this when i query like

select * from dispensary where OPNo='011650/16'

| OPDNo |DispensedDate | DrugName | CreatedBy|
| 011650/16 | 6/29/2016 |folic acid| admin |
| 011650/16 | 6/21/2016 |fersolate | asah |


That is I want only one record for every OPNo on a particular date. If an OPDNo has one or more entries on the same date, then the query should return only entry. Probably the first entry for each date if an OPDNo has more entries on that particular date.

Answer

You can try this...

select * from dispensary do
where do.Drugname in (select max(Drugname) from dispensary di
                      where di.OPDNo = do.OPDNo
                      and di.DispensedDate = do.DispensedDate)
and do.OPDNo = '011650/16'

but keep in mind that the result will not be the first drug