Posted by markc600 on 02/25/06 11:14

My mistake, you can get the medians using this


CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1

GO

SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class

• Next in forum: Re: DELETE FROM - Somethings not right
• Prev in forum: Party time: 75% discount for Database Workbench licenses!
• Thread view: Re: median query without using function

[Reply to this message]