Statement

[jawadfrhad]

thread184-39951
hi all
i have a table contain a million records as the following

I want the lowest three prices for each ID (SQL Statement) as

 

[Chris Miller]

This will do. Unless you have cases with the same price for an ID in the top 3.

CREATE CURSOR prices (id int, price int)
INSERT INTO prices VALUES (1,10)
INSERT INTO prices VALUES (2,15)
INSERT INTO prices VALUES (3,20)
INSERT INTO prices VALUES (1,5)
INSERT INTO prices VALUES (2,16)
INSERT INTO prices VALUES (1,17)
INSERT INTO prices VALUES (3,14)
INSERT INTO prices VALUES (1,30)
INSERT INTO prices VALUES (2,21)

INDEX on id TAG id
INDEX on price TAG price


* Minprices query
SELECT Id, MIN(price) as price1 FROM prices ;
GROUP BY id INTO CURSOR prices1 readwrite

SELECT prices.Id, MIN(price) as price2 FROM prices ;
INNER JOIN prices1 ON prices.id = prices1.id;
WHERE prices.price>prices1.price1;
GROUP BY prices.id INTO CURSOR prices2

SELECT prices.Id, MIN(price) as price3 FROM prices ;
INNER JOIN prices2 ON prices.id = prices2.id;
WHERE prices.price>prices2.price2;
GROUP BY prices.id INTO CURSOR prices3


SELECT prices1.Id, price1, price2, price3;
FROM prices1;
LEFT JOIN prices2 ON prices2.id = prices1.id ;
LEFT Join prices3 ON prices3.id = prices2.id

Chriss

 

[jawadfrhad]

wow...Thank you

 

[Chris Miller]

How does it work with your large dataset?

Chriss