On Fri, Jul 2, 2010 at 11:35 AM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > I don't know about anybody else but I can't tell what you want to do. > > Have you got some sample data and the results you expect from it? > > > Michael D. Black > Senior Scientist > Northrop Grumman Mission Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Peng Yu > Sent: Fri 7/2/2010 11:29 AM > To: General Discussion of SQLite Database > Subject: EXTERNAL:Re: [sqlite] How to select an entry that appears <=n times > and only show n times if it appears more than n times? > > > > On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote: >> On Fri, Jul 2, 2010 at 11:19 AM, P Kishor <punk.k...@gmail.com> wrote: >>> On Fri, Jul 2, 2010 at 11:15 AM, Peng Yu <pengyu...@gmail.com> wrote: >>>> Hi, >>>> >>>> SELECT DISTINCT type_id FROM foods; >>>> >>>> If I use 'distinct', any entry that shows up greater or equal to one >>>> time will only appear once. But I want to select an entry that appears >>>> <=n times and only show n times if it appears more than n times. I >>>> think that "group by" might help. But I'm not familiar with SQL enough >>>> yet. Would you please let me know what command to use? >>>> >>> >>> Try >>> >>> SELECT <whatever column>, Count(type_id) >>> FROM foods >>> GROUP BY <whatever column> >>> HAVING Count(type_id) < n >> >> make that >> >> HAVING Count(type_id) <= n > > But this doesn't show anything that count more than n times. I want > the type_id shows up more than n times in the database only appear n > times in the result of the query. > > >
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. I would solve it using a programming language. Something like SELECT type_id WHERE type_id = 5 LIMIT n UNION SELECT type_id WHERE type_id = 8 LIMIT n UNION except. LIMIT, afaik, is applied *after* all the UNIONs. -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
