I have the following query built based on some advice I got here earlier.
What I hope to find is a list of all of the records where the same chartnum has duplicated medications. What it seems to bring back is one row per medication and the count tells me how many times that medication is in the table (or how many different chartnums have that med)
Can someone tweak this code to work as I desire?
select * from (
, count(medication) over (partition by medication) counter
FROM [Rx].[dbo].[MedicationList] (nolock)
where org = 'BNMG') der where der.counter > 1
order by chartnum, medication