SQL Server Performance

SQL Query Help

Discussion in 'General Developer Questions' started by 99redcamaro, Jun 21, 2004.

  1. 99redcamaro New Member

    hello guys
    i'm trying to figure it out a way to do this

    i have a table with the following data

    plan_ID---trans_id --- Price ---

    1 -------- 10m ------- $3
    1 -------- 11p ------- $0.925
    2 -------- 11p ------- $4.50
    3 -------- 11p ------- $3.75
    2 -------- 10m ------- $0.50
    4 -------- 11p ------- 2

    i need to get something like a least cost report
    ex: i need to find the cheap price per trans_id and plan_id
    in this example that would be

    trans_id=10m, plan_id=1 price =$0.50

    that's easy to get, but i wonder how can i get the 2nd cheapest one, in this particular case trans_id=10m, plan id=2 price=$3

    for a better example:
    there is only a trans_id unique per plan_id as you can see on the table

    least price choices for trans_id=11p is:

    1st choice: trans_id=11p, plan_id=1, price $0.925
    2nd choice: trans_id=11p, plan_id=4, price $2
    3rd choice: trans_id=11p, plan_id=3, price $3.75
    4rd choice: trans_id=11p, plan_id_2, price $4.50

    the idea is to have a query that can do that, slect the first cheap price, then the second cheap price, and so for,based on the first 5 cheap prices would be more than enough,
    the problem is, i have no idea how to write a sql statement that can do this [:I]

    could you guys help me???

    thanks


Share This Page