I have these tables:
person(pid, name,email,phone,city)
ride(rid,pid,date,spots,start,target) [rideID, personID- the person who offers the ride, spots= open slots in the ride, start and target are the destinations]
participate(pid,rid)- person pid participates in ride rid
I have to satisfy the function generousUsers(k)
generosity ranking is defined by the number of rides the person offers. I have to find the Kth generous users by this definition (there may be more than one) or return NULL if we have less than K people on the list.
the PID in ride is the offerer and pid in the participate relationship are the passengers
I can not use recursion in this query as this isn't SQL3.
I've tried something like
Code:
Select pid From
(Select pid, count(*) as ranking, row_number() OVER (Order By ranking Desc) as Rownum
From Ride
Group By pid
Order By ranking desc)
Where Rownum < K
But I don't know how to return the EXPLICIT Kth generous user and if we have a few tied-K, I have to return all of them ordered by the user who has the earliest date that has offered a ride.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
