SQL Query Required !
-
Hello, I have a table Order. There is a field called "Symbol", another field called "Price",and several other fields. Now I want to select the minimum price records with distinct "Symbol". Id Symbol Price Volume Col1 Col2 Col3... --------------------------------------------------------------------- 1 AAA 19 1000 xxxx xxxx xxxx 2 AAA 40 4000 xxxx xxxx xxxx 3 CBC 150 50000 xxxx xxxx xxxx 4 CBC 149 4000 xxxx xxxx xxxx I want records 1 and 4 to be selected, since CBC has shortest price 149 and AAA has shortest price 19. Can anyone help me out in this regard? Thanks in advance
-
Hello, I have a table Order. There is a field called "Symbol", another field called "Price",and several other fields. Now I want to select the minimum price records with distinct "Symbol". Id Symbol Price Volume Col1 Col2 Col3... --------------------------------------------------------------------- 1 AAA 19 1000 xxxx xxxx xxxx 2 AAA 40 4000 xxxx xxxx xxxx 3 CBC 150 50000 xxxx xxxx xxxx 4 CBC 149 4000 xxxx xxxx xxxx I want records 1 and 4 to be selected, since CBC has shortest price 149 and AAA has shortest price 19. Can anyone help me out in this regard? Thanks in advance
-
Thanks for the reply but the problem is not that simple.. I want to select the whole record not just 2 fields.. and when I select all records the distinct stops working.. I tried sub query.. didn't work..
-
Hello, I have a table Order. There is a field called "Symbol", another field called "Price",and several other fields. Now I want to select the minimum price records with distinct "Symbol". Id Symbol Price Volume Col1 Col2 Col3... --------------------------------------------------------------------- 1 AAA 19 1000 xxxx xxxx xxxx 2 AAA 40 4000 xxxx xxxx xxxx 3 CBC 150 50000 xxxx xxxx xxxx 4 CBC 149 4000 xxxx xxxx xxxx I want records 1 and 4 to be selected, since CBC has shortest price 149 and AAA has shortest price 19. Can anyone help me out in this regard? Thanks in advance
Hey, another place where my correlation trick is useful!
SELECT Order.*
FROM Order
INNER JOIN
(
SELECT Symbol, MIN(Price) AS MinPrice
FROM Order
GROUP BY Symbol
) O2 ON Order.Symbol = O2.Symbol AND Order.Price = O2.MinPriceCaveat: this trick won't work directly if there's more than one row for a given Symbol with the minimum Price value. If there is you'll get every row with that price. Stability. What an interesting concept. -- Chris Maunder
-
Hey, another place where my correlation trick is useful!
SELECT Order.*
FROM Order
INNER JOIN
(
SELECT Symbol, MIN(Price) AS MinPrice
FROM Order
GROUP BY Symbol
) O2 ON Order.Symbol = O2.Symbol AND Order.Price = O2.MinPriceCaveat: this trick won't work directly if there's more than one row for a given Symbol with the minimum Price value. If there is you'll get every row with that price. Stability. What an interesting concept. -- Chris Maunder
Hey.. this solves my problem.. Thanks a lot.. Best Regards, Zishan