Help needed in nested(?) SQL query

[PoPo99]

Hi,

I'm newbie to SQL and need some help in basic query.
I think I should do some kind of "nested" query but don't know how...

I have a table which has columns;
EventID
Timestamp
Selection
Odds

Data in table looks for example like this;

EventID;Timestamp;Selection;Odds
10001;14:01;Kidd;1.89
10001;14:01;Smith;2.12
10001;14:01;Jones;3.10
10001;14:05;Kidd;1.78
10001;14:05;Smith;2.08
10001;14:05;Jones;3.25
10001;14:08;Kidd;1.92
10001;14:08;Smith;2.00
10001;14:08;Jones;3.35
10003;14:15;Roddick;4.15
10003;14:15;Avenue;3.30
10003;14:15;Torr;1.80
10003;14:20;Roddick;4.30
10003;14:20;Avenue;3.10
10003;14:20;Torr;1.84
10003;14:25;Roddick;4.40
10003;14:25;Avenue;3.35
10003;14:25;Torr;1.76

My mission is to make a query which selects lowest "Odds" for every individual "Selection" in
every individual "EventID". So in above table data example query should return this;

10001;14:05;Kidd;1.78
10001;14:08;Smith;2.00
10001;14:01;Jones;3.10
10003;14:25;Torr;1.76
10003;14:20;Avenue;3.10
10003;14:15;Roddick;4.15

It is noticeable that same "Selection" name can exist in different "EventID"'s.

Your help is much appreciated!
Thank you in advance.

J

[skhanal]

select * from table1 as a
join
(select EventID, selection, min(odds) as min_odds
from table1
group by EventID, selection) as b
on a.EventID=b.EventID and a.selection=b.selection and a.odds=b.min_odds