Andrea,
This appears to do what you want...
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite> create table tst( name text, score integer, info text );
sqlite> insert into tst values( 'A', 289, 'A1' );
sqlite> insert into tst values( 'C', 29, 'C1' );
sqlite> insert into tst values( 'A', 29, 'A2' );
sqlite> insert into tst values( 'C', 129, 'C2' );
sqlite> insert into tst values( 'C', 19, 'C3' );
sqlite> insert into tst values( 'A', 1129, 'A3' );
sqlite> insert into tst values( 'B', 19, 'B1' );
sqlite> insert into tst values( 'A', 19, 'A4' );
sqlite> insert into tst values( 'B', 9, 'B2' );
sqlite> insert into tst values( 'B', 99, 'B3' );
sqlite>
sqlite> select * fro tst;
SQL error: near "fro": syntax error
sqlite> select * from tst;
A|289|A1
C|29|C1
A|29|A2
C|129|C2
C|19|C3
A|1129|A3
B|19|B1
A|19|A4
B|9|B2
B|99|B3
sqlite> select tst.* from tst cross join
...> ( select max(score) as maxS, name from tst group by name ) as subQuery
...> on tst.name=subQuery.name and tst.score = subQuery.maxS;
C|129|C2
A|1129|A3
B|99|B3
sqlite>
Rgds,
Simon
2008/6/6 Andrea Galligani <[EMAIL PROTECTED]>:
> Hi to all,
>
> I'm a novice in SQL and SQLite so I apologize if this question has an
> obvious solution
>
> I have a table formed in this way.
>
> NAME SCORE INFO1 INFO2 etc.
> Andrew 5 aaa bbb ...
> Andrew 8 ddd eee ...
> Paul 4 xxx yyy ...
> Paul 6 aaa fff ...
>
> I need a query to get from any name the row with the max score. So from
> the above sample, I would like to obtain the following rows.
>
> NAME SCORE INFO1 INFO2 etc.
> Andrew 8 ddd eee ...
> Paul 6 aaa fff ...
>
> Can I get this result using a complex query or should I filter the rows
> using any different tool from SQL (C/C++)?
>
> Thanks in advance
> Andrea
>
> --
> --------------------------------------
> Andrea Galligani
>
> Macs Tech s.r.l.
> Via San Paolo 11, 56125
> Pisa - Italy
>
> Phone...: +39 050 40915
> e-mail..: [EMAIL PROTECTED]
> --------------------------------------
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
