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

Groups > comp.lang.python > #38129

Re: Improve reduce functions of SQLite3 request

Path csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!newsfeed.xs4all.nl!newsfeed4.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Return-Path <python-python-list@m.gmane.org>
X-Original-To python-list@python.org
Delivered-To python-list@mail.python.org
X-Spam-Status OK 0.005
X-Spam-Evidence '*H*': 0.99; '*S*': 0.00; 'output': 0.04; 'matches': 0.07; '-------': 0.09; 'iterate': 0.09; 'received:80.91': 0.09; 'received:80.91.229': 0.09; 'received:gmane.org': 0.09; 'received:list': 0.09; 'distinct': 0.16; 'keys)': 0.16; 'name...': 0.16; 'received:80.91.229.3': 0.16; 'received:dip.t-dialin.net': 0.16; 'received:plane.gmane.org': 0.16; 'received:t-dialin.net': 0.16; 'wrote:': 0.17; 'subject:request': 0.17; 'issue.': 0.20; 'skip:= 10': 0.20; 'suggested': 0.20; 'trying': 0.21; 'stick': 0.22; 'tables': 0.23; 'header:User-Agent:1': 0.26; 'looks': 0.26; 'leave': 0.26; 'select': 0.26; 'header:X-Complaints-To:1': 0.28; "i'm": 0.29; 'maybe': 0.29; 'primary': 0.30; 'sense': 0.31; '-----': 0.32; 'could': 0.32; 'club': 0.33; 'ones,': 0.33; 'to:addr:python-list': 0.33; 'list': 0.35; 'similar': 0.35; 'there': 0.35; 'received:org': 0.36; 'but': 0.36; 'should': 0.36; 'data': 0.37; 'subject:: ': 0.38; 'mark': 0.38; 'to:addr:python.org': 0.39; 'list,': 0.39; 'where': 0.40; 'header:Received:5': 0.40; 'your': 0.60; 'skip:n 10': 0.63; 'here': 0.65; 'limit': 0.65; 'home': 0.66; 'union': 0.66; 'clubs': 0.71; 'participants': 0.78; 'club,': 0.91; 'dennis': 0.91
X-Injected-Via-Gmane http://gmane.org/
To python-list@python.org
From Peter Otten <__peter__@web.de>
Subject Re: Improve reduce functions of SQLite3 request
Date Mon, 04 Feb 2013 17:29:41 +0100
Organization None
References <kelbl0$1jv$1@nerdhammel.gnuher.de> <mailman.1317.1359915110.2939.python-list@python.org> <keogmb$dml$1@nerdhammel.gnuher.de>
Mime-Version 1.0
Content-Type text/plain; charset="UTF-8"
Content-Transfer-Encoding 8Bit
X-Gmane-NNTP-Posting-Host p5084b0cb.dip.t-dialin.net
User-Agent KNode/4.7.3
X-BeenThere python-list@python.org
X-Mailman-Version 2.1.15
Precedence list
List-Id General discussion list for the Python programming language <python-list.python.org>
List-Unsubscribe <http://mail.python.org/mailman/options/python-list>, <mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive <http://mail.python.org/pipermail/python-list/>
List-Post <mailto:python-list@python.org>
List-Help <mailto:python-list-request@python.org?subject=help>
List-Subscribe <http://mail.python.org/mailman/listinfo/python-list>, <mailto:python-list-request@python.org?subject=subscribe>
Newsgroups comp.lang.python
Message-ID <mailman.1325.1359995325.2939.python-list@python.org> (permalink)
Lines 77
NNTP-Posting-Host 2001:888:2000:d::a6
X-Trace 1359995325 news.xs4all.nl 6960 [2001:888:2000:d::a6]:53724
X-Complaints-To abuse@xs4all.nl
Xref csiph.com comp.lang.python:38129

Show key headers only | 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