Igor Tandetnik <itandet...@mvps.org> wrote:
> P Kishor <punk.k...@gmail.com> wrote:
>> I think what Peng wants is that given table of type_id
>>
>> 5
>> 5
>> 5
>> 5
>> 5
>> 4
>> 4
>> 4
>> 7
>> 7
>> 8
>> 8
>> 8
>> 8
>>
>> if 'n' is 3, the desired result is
>>
>> 5
>> 5
>> 5
>> 4
>> 4
>> 4
>> 7
>> 7
>> 8
>> 8
>> 8
>>
>> I don't know how to do that with sql.
>
> Well, if you insist:
>
> select type_id from foods f1
> where (
> select count(*) from foods f2
> where f2.type_id = f1.type_id
> and f2.rowid < f1.rowid) < 3;
Or this - same thing really, but may be easier on the eyes:
select type_id from foods f1
where rowid in (
select rowid from foods f2
where f2.type_id = f1.type_id
order by rowid limit 3);
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
