sql - Join to nth highest value in another table in MySQL -

i know how join max or min value of column table, example:

select id, user_id, date_created, invite_date user inner join ( select user_referral.user_id, min(user_referral.date_created) invite_date user_referral group user_referral.user_id) invtable on user.id = invtable.user_id 

but how do nth highest or lower value of user_referral.date_created instead of max or min?

just use limit/offset:

select u.id, u.user_id, u.date_created, u.invite_date user u inner join      (select ref.*       user_referral ref       group ref.user_id       order ref.date_created       limit 1 offset 8      ) invtable      on u.id = invtable.user_id; 

just note, returns ninth row, because offset counting starts @ 0 , not 1.

edit:

now see. can way:

select u.id, u.user_id, u.date_created, u.invite_date user u inner join      (select ref.*,              (@rn := if(@uid = ref.user_id, @rn + 1,                         if(@uid := ref.user_id, 1, 1)                        )              ) rn       user_referral ref cross join            (select @rn := 0, @uid := -1) vars       order ref.user_id, ref.date_created      ) invtable      on u.id = invtable.user_id rn = 8;