Top 2 values for each record

[Mary10k]

Hello,

I have a table called Labs and a table called EE's. Each EE has many labs in the lab table. I am trying to figure out how I find the top 2 labs (based on date) and return that along with all of the fields in the EE table. Would I use a sub query?
Something like this? SELECT * FROM ee, WHERE ee.id in (select top 2 from labs). This didn't seem to work.
Thank you.
Mary10k

 

[r937]

the TOP keyword doesn't work in mysql, whether in a subquery or not

what version of mysql are you on?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[steven290]

you can try something like this (play with it)

select lab,max(datefield),ee.*
from labtb
left join ee
on ee.id=labtb.eeid
group by lab
limit 2

not tested though