SQL - Duplicate Rows 2

[NikkieG]

Hi!

I'm hoping someone can help with this as I am at the end of my tether!

I have a table that contains patient data and each record has a cost attached to them. By looking at the patient ID, the date they were admitted and their treatment I have created a unique ID that can identify those patients who were seen more than once on the same day. However, where there is more than one record the costs for treatment can be different and so we just want to pull out a list of those with the lowest cost. I have tried using the min function, but am not getting the results I expect because there are several duplicate pairs. The current data looks something like this:

UniqueID Tariff
200510254081332436501 518.7900
200510254081332436501 911.3000
200511074100033451303 373.3600
200511074100033451303 3705.8100
200510164140524308501 518.7900
200510164140524308501 1399.3700

But I would like to see just the minimum from each pair:

UniqueID Tariff
200510254081332436501 518.7900
200511074100033451303 373.3600
200510164140524308501 518.7900

Sorry for such a long query, but can anyone help?

Many thanks in advance,


Nik

 

[bborissov]

SELECT UniqueId, MIN(Tariff) AS Tariff
       FROM MyTable
GROUP BY UniqueId

Borislav Borissov

 

[NikkieG]

Sorry, I forgot to say that using

select UniqueID, min(Tariff)
from
dbo.BHT_QA8D_EXTRACT_Dec05
group by UniqueID

works for pairs, but I can't figure out what to do with rows where there are three values. I realise that SQL is doing what I ask it to, but for those where there are three records I actually want all rows that aren't the maximum tariff returned - is this possible? Is there a way of getting it to return everything that isn't the max tariff?

Thanks.


N

 

[bertrandkis]

This query will do the trick for you.
This technique is called correlated subquery.

select UniqueID, Tariff
from
dbo.BHT_QA8D_EXTRACT_Dec05 as T1
where Exists(Select Tariff from
dbo.BHT_QA8D_EXTRACT_Dec05 as T2
where T1.UniqueID=T2.UniqueID
and T1.Tariff < T2.Tariff)

You can also do it with a self join like this:
select T1.UniqueID, T1.Tariff
from
dbo.BHT_QA8D_EXTRACT_Dec05 as T1
INNER JOIN dbo.BHT_QA8D_EXTRACT_Dec05 as T2
ON T1.UniqueID=T2.UniqueID and T1.Tariff < T2.Tariff

Now you've got 2 ice cream flavours. Vanilla and chocolate choose what you like.
Enjoy

 

[bborissov]

CREATE TABLE #Temp (UniqueID varchar(50), tariff numeric (10,4), Test int)
INSERT INTO #Temp VALUES ('200510254081332436501',518.7900,1)
INSERT INTO #Temp VALUES ('200510254081332436501',911.3000,2)
INSERT INTO #Temp VALUES ('200510254081332436501',932.3000,3)
INSERT INTO #Temp VALUES ('200511074100033451303',373.3600,1)
INSERT INTO #Temp VALUES ('200511074100033451303',3705.8100,2)
INSERT INTO #Temp VALUES ('200510164140524308501',518.830,1)
INSERT INTO #Temp VALUES ('200510164140524308501',1399.3700,2)

SELECT #Temp.UniqueID, #Temp.tariff, #temp.Test
       FROM #temp
RIGHT JOIN 
(SELECT UniqueID, Min(Tariff) AS Tariff FROM #temp GROUP BY UniqueId) T
 ON #temp.UniqueID = T.UniqueId AND #temp.Tariff = T.Tariff
drop table #temp

Borislav Borissov

 

[NikkieG]

Thanks Borislav - this does answer my original question, but still only brings back the minimum value of the group of three, whereas in cases of three records I need all but the most expensive returning.

In your example I would need rows 1 & 2 returning for UniqueId '200510254081332436501', yet I only get row 1.

Thanks for your help though.


Nik

 

[NikkieG]

Thanks so much bertrandkis - I had had a play with both bits of code and, whilst the second one was giving me slightly odd answers when I had triple rows (I got one too many results, presumably from the 'T1.Tariff < T2.Tariff' part of the join - it joined row 1 to 2, 1 to 3 and then 2 to 3), the first query worked perfectly.

You're a complete hero!

Thanks again.


Nik

 

[bborissov]

I misundestood you question. I think you want only the cheepest record ))))

Borislav Borissov