Complex query: limit occurences of certain value

[stuart1980]

Lets say we have a 'person' table and a 'car' table. And the primary key for these tables are person_id and car_id respectively. A person can have many cars.

How could you select the first 3 cars for each person in ONE query? I don't want to requery for each person, nor do I want to filter the result set in my code.

Thanks in advance.

 

[r937]

you didn't say what the fk from car to person was, so i'll call it car.owner_id

you didn't say what sequence "first 3" refers to, so i'll assume it's the date the car was acquired

i haven't tested this but i think i got the inequalities in the right direction :grin:

select person_id, car_id, car.acquired
  from person
inner
  join car X
    on person_id = car.owner_id
 where 3 > 
       ( select count(*) 
           from car
          where owner_id = person.person_id
            and acquired < X.acquired ) 
order 
    by person_id
     , car.acquired

the "top n" query (as that subquery is more commonly known) is a lot simpler if your database supports the TOP or LIMIT keyword

rudy

http://rudy.ca/

 

[angiole]

Is the DB Oracle?
AA 8~)