Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #38129

Re: Improve reduce functions of SQLite3 request

Show all headers | View raw

Steffen Mutter wrote:

> Dennis Lee Bieber wrote:
>> Untested:
>>
>> SELECT DISTINCT * from
>> (select homenr as nr, home as club FROM Runde20122013
>> WHERE place="karlsruhe"
>> UNION SELECT guestnr as nr, guest as club FROM 20122013
>> WHERE place="karlsruhe")
>> limit 10
> 
> Hi Dennis,
> 
> here the output of your suggested solution:
> SELECT DISTINCT * FROM (
>   SELECT HeimNr as nr, Heim as club FROM Runde20122013
>     WHERE kreis ="karlsruhe"
> UNION
>   SELECT GastNr as nr, gast as club FROM Runde20122013
>     WHERE kreis ="karlsruhe") LIMIT 10;
> 
> 359|TV Calmbach
> 21101|SG Heidel/Helm
> 21236|JSG Neuth/Büch
> 23108|TG Eggenstein
> 23108|TGEggenstein 2 <-
> 23109|TV Ettlingenw
> 23109|TV Ettlingenw 2 <-
> 23112|TSV Jöhlingen
> 23112|TSV Jöhlingen 2 <-
> 23112|TSV Jöhlingen 3 <-
> 
> Still not like what I'm looking for.
> Maybe I should iterate through the list, pick out the nr and look for
> the club stick it to a new list and leave out those ones, with the longe
> r club name...

How about

SELECT nr, min(club) FROM (
  SELECT HeimNr as nr, Heim as club FROM Runde20122013
    WHERE kreis ="karlsruhe"
UNION
  SELECT GastNr as nr, gast as club FROM Runde20122013
    WHERE kreis ="karlsruhe")
GROUP BY nr;

However, I'm smelling a data normalization issue. It looks like you are 
interested in the club, but that there may be multiple teams per club and 
you are trying to derive the club from the team name. If that's the case you 
should consider a database layout with tables similar to the following (* to 
mark primary keys)

matches
-------
matchID*, hometeamID, guestteamID, ...

teams
-----
teamID*, clubID, teamname, ...

clubs
-----
clubID*, clubname, ...

With such a layout you could get all clubs with (untested, of course)

SELECT clubs.clubId, clubs.clubname FROM (
    SELECT hometeamID as teamID from matches
UNION
    SELECT guestteamID as teamID from matches) as participants
INNER JOIN teams on teams.teamID = participants.teamID
INNER JOIN clubs on teams.clubID = clubs.teamID;

I hope you can make sense of it...

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-03 09:46 +0000
  Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-03 13:11 -0500
    Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-04 14:30 +0000
      Re: Improve reduce functions of SQLite3 request Dave Angel <davea@davea.name> - 2013-02-04 10:30 -0500
      Re: Improve reduce functions of SQLite3 request Peter Otten <__peter__@web.de> - 2013-02-04 17:29 +0100
      Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-04 15:45 -0500
        Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-04 22:05 +0000
          Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-04 18:47 -0500
            Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-06 09:18 +0000
              Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-06 12:26 -0500
                Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-06 23:33 +0000
                Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-07 00:40 -0500
                Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-07 11:33 +0000
                Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-07 13:03 -0500
                Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-07 22:54 +0000
                Re: Improve reduce functions of SQLite3 request Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2013-02-08 12:17 -0500
                Re: Improve reduce functions of SQLite3 request Steffen Mutter <steffen@webanimations.de> - 2013-02-08 19:59 +0000

csiph-web