Re: [sqlite] selecting the top 3 in a group

Thanks, Igor. That worked perfectly. Time for me to read up on rowid and the subtleties of subselects. Regards, - Robert On Thu, Jan 8, 2009 at 6:48 AM, Igor Tandetnik wrote: > select div, team from teams t1 where rowid in > (select rowid from teams t2 where t1.div =

Re: [sqlite] selecting the top 3 in a group

"Robert Citek" wrote in message news:4145b6790901072206u73b367f1g3d377bb6962de...@mail.gmail.com > How can I construction a SQL query to pick the top three (3) items in > a group? > > I have a list of sports teams which are grouped into divisions, say A, > B, C, D, etc.

Re: [sqlite] selecting the top 3 in a group

--- robert.ci...@gmail.com wrote: > I am still curious to know if there is a purely SQL way to do the same. This can be achieved using group_concat: select div, rtrim (substr (s, 1, 10)) nr1, rtrim (substr (s, 1, 10)) nr2, rtrim (substr (s, 1, 10)) nr3 from ( select div,

Re: [sqlite] selecting the top 3 in a group

Turning the pseudo code into a bash script produced the desired output: for i in $(sqlite3 team.db 'select distinct div from teams ' ) ; do sqlite3 -separator $'\t' team.db ' select div, team, wins from teams where div="'$i'" order by wins+0 desc limit 3 ;' done I am still

Re: [sqlite] selecting the top 3 in a group

That gets me the best team in the first five divisions. I would like the top three teams within each division. Regards, - Robert On Thu, Jan 8, 2009 at 12:19 AM, aditya siram wrote: > Hi Robert, > SQL has a LIMIT keyword. I have used it to take the top 'x' entries of a

Re: [sqlite] selecting the top 3 in a group

Hi Robert, SQL has a LIMIT keyword. I have used it to take the top 'x' entries of a large table , so for example: SELECT * from table LIMIT 20 You should be able to use it in your query like so: select div, team, max(wins) from teams group by div limit 5; -deech On Thu, Jan 8, 2009 at 12:06 AM,

[sqlite] selecting the top 3 in a group

How can I construction a SQL query to pick the top three (3) items in a group? I have a list of sports teams which are grouped into divisions, say A, B, C, D, etc. At the end of the season I would like to get a list of the top three teams (those with the most wins) in each division. If I wanted