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

Groups > comp.lang.python > #38138

Re: Improve reduce functions of SQLite3 request

Show key headers only | View raw

On Mon, 4 Feb 2013 14:30:35 +0000 (UTC), Steffen Mutter
<steffen@webanimations.de> declaimed the following in
gmane.comp.python.general:


> 
> 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.

	I presume you mean the " 2" and " 3" on those entries should not be
considered when matching "distinct".

	I suspect you have a poorly normalized database (what does that
trailing number identify? Heck, are the leading initials unique to the
subsequent name?). The trailing number should probably be something
stored as a separate field. If the initials are unique, they should be a
separate field used as a foreign reference to retrieve the longer name.

	For MySQL, MAYBE (there is a single space in the ' ')

SELECT DISTINCT nr, substring_index(cb, ' ', 2) as club FROM (
  SELECT HeimNr as nr, Heim as cb FROM Runde20122013 
    WHERE kreis ="karlsruhe" 
UNION 
  SELECT GastNr as nr, gast as cb FROM Runde20122013 
    WHERE kreis ="karlsruhe") LIMIT 10; 

	This function keeps only the part before the 2nd space (if such is
found) in the club name.

	Oh wait -- you are using SQLite3... SQLite3 doesn't have the
substring_index function, and I don't even find an index function.
However, you CAN write a Python function to do the equivalent, and
register it with SQLite3 so that it can be called from within the SQL
statement. I'm not going to demonstrate that, though it may be better
than another alternative:

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

	Let's see...
E:\UserData\Wulfraed\My Documents>sqlite3 demo
SQLite version 3.6.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test
   ...> (
   ...>         ID integer primary key autoincrement,
   ...>         nr integer,
   ...>         club varchar(50)
   ...> );
sqlite> insert into test (nr, club) values (359, "TV Calmbach");
sqlite> insert into test (nr, club) values (21101,"SG Heidel/Helm");
sqlite> insert into test (nr, club) values (23112,"TSV Johlingen 3");
sqlite> insert into test (nr, club) values (23109,"TV Ettlingenw 2");
sqlite> insert into test (nr, club) values (23112,"TSV Johlingen");
sqlite> insert into test (nr, club) values (23109,"TV Ettlingenw");
sqlite> insert into test (nr, club) values (23108,"TG Eggenstein");
sqlite> insert into test (nr, club) values (21236,"JSG Neuth/Buch");
sqlite> insert into test (nr, club) values (23112,"TSV Johlingen 2");
sqlite> insert into test (nr, club) values (23108,"TG Eggenstein 2");
sqlite> .mode column
sqlite> .head on
sqlite> .width 10, 25, 25
sqlite> select * from test;
ID          nr                         club
----------  -------------------------  -------------------------
1           359                        TV Calmbach
2           21101                      SG Heidel/Helm
3           23112                      TSV Johlingen 3
4           23109                      TV Ettlingenw 2
5           23112                      TSV Johlingen
6           23109                      TV Ettlingenw
7           23108                      TG Eggenstein
8           21236                      JSG Neuth/Buch
9           23112                      TSV Johlingen 2
10          23108                      TG Eggenstein 2
sqlite> select nr, min(club) from test group by nr;
nr          min(club)
----------  -------------------------
359         TV Calmbach
21101       SG Heidel/Helm
21236       JSG Neuth/Buch
23108       TG Eggenstein
23109       TV Ettlingenw
23112       TSV Johlingen
sqlite>

	Don't even need the DISTINCT with the GROUP BY.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

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