test test test test - 10 months ago 34
SQL Question

SQL query to select another row with minimum and maximum of nid value

I have a 3 columns

nid aid cst
1 1 23
2 3 45
3 1 67
4 2 34
5 3 12
6 1 6

Please re-read for explanation:
I have to find the cst of aid which has minimum value of nid for example whne i select aid=1 then it must give 23 as
it correponds to mimimum value of
nid(1) and when i select 3 it must give 45 as it has least nid(2) as compared to all other nids of aid

My try is this ad it do not work :

select cst from tbl
where (nid) IN (select min(nid) from tbl) and aid=nid

I also have to do the same for max as i done for min.

select cst
from tbl 
where aid=1 and nid=(select min(nid) from tbl where aid=1)

This query will give you minimum values of cst for all values of aid