MySQL Forums
Forum List  »  Newbie

Re: how to retrieve specific number of rows
Posted by: Roland Bouman
Date: August 04, 2005 01:58PM

well, if you have a unique combination of columns, you could do:

select t1.c1
, t1.c2
...and all other columns of t1
from t t1
inner join t t2
on t1.c1 >= t2.c1
group by t1.c1
, t1.c1
...and all other columns of t1
having count(t2.c1) > 5;

here i assume c1 is a unique column.
Or, with subqueries:

select *
from t t1
left join (
select *
from t
limit 5
) t2
on t1.c = t2.c
where t2.c is null
;

BTW I tried i this:

select * from t t1
where exists (select t2.c from t t2 where t1.c = t2.c limit 5);

first, but to my astonishment, it returns all rows. My hunch is that the limit clause is ignored here, because when I modified it to be an IN subquery:

select * from t t1
where t1.c in (select t2.c from t t2 limit 5);

I'm getting an error indicating that limit is not supported in that case. Does anyone know if this is a good hunch?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.