sql help

[Chilly]

Got a table that looks like:

country, runners, winners, length, lambda, mse

with shitloads of lines in.

I do this to get an intermediate result:

select country, round(length, -1) as len, runners, winners, lambda, avg(mse) as mse, count(*) as n from dh_analysis_raw
group by country, `len`, runners, winners, lambda
order by mse asc

What I now need is to get the lambda value associated with the least mse for each country,len,runners,winners tuple.

Ie I want a dataset that includes every unique country,len,runners,winns tuple and the lambda that's in the row with the smallest mse value.

I'm fucked if I can work out how to do it in SQL...any ideas ?

Here's some sample rows from the intermediate result from the query above:

IRE 1210 3 2 0.5200000000000001 0.0000027934841344635365 1
IRE 1210 3 2 0.5300000000000001 0.000005568927045702544 1
IRE 1210 3 2 0.5100000000000001 0.000006513016269177977 1
IRE 1210 3 2 0.5400000000000001 0.000014454232310977932 1
IRE 1210 3 2 0.5000000000000001 0.000017124524738913295 1
IRE 1210 3 2 0.5500000000000002 0.000029076341799241323 1
IRE 4220 3 2 0.48000000000000015 0.000033526894372810645 1
IRE 1210 3 2 0.49000000000000016 0.00003503668224757759 1
GB 5630 6 2 0.39000000000000007 0.000035817634807496316 2
GB 5630 6 2 0.4000000000000001 0.00003604576635304941 2
IRE 4220 3 2 0.49000000000000016 0.00003611194525971107 1
GB 5630 6 2 0.38000000000000006 0.000036220399178577985 2
GB 5630 6 2 0.4100000000000001 0.000036908645652801995 2
GB 5630 6 2 0.37000000000000005 0.000037250140026110475 2

 

[- English -]

Probably way above my head but have you tried making a query using "select distinct ..."?

 

[Chilly]

yep - it gets you half way but doesnt do the selective min()'ing I need.

 

[Moriath]

think you will need to do some pl sql not just sql for that .. tho i might be wrong its been a very long time

 

[Keitanz]

Forgive me if it sounds like a silly question, but couldn't you knock up a quick script to do it.?

 

[Moriath]

sql is the scripting language cause its inside the database so the language to script with is pl sql

 

[Chilly]

Its not oracle so cant use pl/sql. Moriath, yeah I can easily write a php or java prog to do it, but I'm sure this must be possible in regular SQL.

 

[Chilly]

Did it! Gotta use an intermediate result as a second table to pick results from the first. You can get the min(mse) from one and use it to grab the lambda from the other.

 

[Moriath]

Its not oracle so cant use pl/sql. Moriath, yeah I can easily write a php or java prog to do it, but I'm sure this must be possible in regular SQL.

Ah I only done the oracle one

 

[TdC]

I'll get back to you after next semester