>Sir all solutions provided from all participants work fine.
>
>Thanks for helping
You need to check if there are scenarios that can give a wrong result. Not sure if this data can happen, but this seems to give the wrong record
If you add records with large amounts in qty:
CREATE CURSOR junk(id n(3),qty n(5))
INSERT INTO junk values(1,500)
INSERT INTO junk values(2,600)
INSERT INTO junk values(3,700)
INSERT INTO junk values(4,700)
INSERT INTO junk values(5,700)
INSERT INTO junk values(6,700)
INSERT INTO junk values(7,700)
INSERT INTO junk values(8,700)
INSERT INTO junk values(9,700)
INSERT INTO junk values(10,800)
INSERT INTO junk values(11,900)
INSERT INTO junk values(12,1000)
INSERT INTO junk values(13,1100)
INSERT INTO junk values(14,1200)
INSERT INTO junk values(15,1300)
INSERT INTO junk values(16,1300)
INSERT INTO junk values(17,1300)
INSERT INTO junk values(18,1400)
INSERT INTO junk values(19,1300)
INSERT INTO junk values(20,1300)
INSERT INTO junk values(21,21000)
SELECT TOP 1 Junk.*;
FROM Junk;
INNER JOIN (SELECT TOP 1 *, Qty * Cnt AS OrdBy ;
FROM (SELECT Qty, COUNT(*) AS Cnt FROM junk GROUP BY Qty) aaa;
ORDER BY OrdBy DESC) jnk ON jnk.Qty = junk.Qty;
ORDER BY Id
Because Qty * Cnt assumes that there are never large differences in qty.
Christian Isberner
Software Consultant