i am trying to write a query that would do the following on the view update_winnerlist which is already GROUPed BY item_index
SELECT email_id (SELECT email_id,min(bid_amount) FROM update_winnerslist AS a1 WHERE a1.item_index = 10 )
i need to perform the above in a single query as i am using it in a trigger to update a table.
asked Apr 26, 2012 at 12:53 AM in Default
@heboy1908 I did not understand your requirement properly. But if you want to hit your table only once then the following could be what you want
Moreover, please keep in mind that there could be ties for the bid amount and if you are doing it in the trigger, you may need to get a random choice or whatever the requirement is. For that the TOP clause may be helpful.
PS: RANK() function could be used as well instead of ROW_NUMBER()
answered Apr 26, 2012 at 04:52 AM