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 = t2.div
> order by wins de
"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. At the end of the season I
--- 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, group_co
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 c
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
> large table , so for ex
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,
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
7 matches
Mail list logo