Seasonal syntax

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Seasonal syntax

Simon Slavin-3
Some SQL terminology:

  Selection Clause: WHERE <condition>
       Sort Clause: ORDER BY <directioned column list>
    Sublist Clause: LIMIT <number> OFFSET <number>
    Subsort Clause: GROUP BY <expression> HAVING <expression>
      Santa Clause: SELECT name,hobbies,address FROM people WHERE behaviour='nice’

Season’s greetings and best wishes to all subscribers.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Seasonal syntax

Stephen Chrzanowski
You had to do that just beclause....

On Tue, Dec 12, 2017 at 12:24 PM, Simon Slavin <[hidden email]> wrote:

> Some SQL terminology:
>
>   Selection Clause: WHERE <condition>
>        Sort Clause: ORDER BY <directioned column list>
>     Sublist Clause: LIMIT <number> OFFSET <number>
>     Subsort Clause: GROUP BY <expression> HAVING <expression>
>       Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour='nice’
>
> Season’s greetings and best wishes to all subscribers.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

2D query

Valentin Davydov-2
In reply to this post by Simon Slavin-3
Hi, all!

Given the following table:

CREATE TABLE people(name,sex);
INSERT INTO people VALUES("Alex","F");
INSERT INTO people VALUES("Alex","M");
INSERT INTO people VALUES("Jane","F");
INSERT INTO people VALUES("John","M");

How to construct a query which returns coalesced sex but individual names,
such as "F: Alex, Jane. M: Alex, John."?

Sincerely,
Valentin Davydov.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2D query

David Raymond
select sex, group_concat(name, ', ') from people group by sex;

(And don't forget to use single quotes for string literals)


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Valentin Davydov
Sent: Wednesday, December 13, 2017 8:57 AM
To: SQLite mailing list
Subject: [sqlite] 2D query

Hi, all!

Given the following table:

CREATE TABLE people(name,sex);
INSERT INTO people VALUES("Alex","F");
INSERT INTO people VALUES("Alex","M");
INSERT INTO people VALUES("Jane","F");
INSERT INTO people VALUES("John","M");

How to construct a query which returns coalesced sex but individual names,
such as "F: Alex, Jane. M: Alex, John."?

Sincerely,
Valentin Davydov.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 2D query

Richard Hipp-3
In reply to this post by Valentin Davydov-2
On 12/13/17, Valentin Davydov <[hidden email]> wrote:

> Given the following table:
>
> CREATE TABLE people(name,sex);
> INSERT INTO people VALUES("Alex","F");
> INSERT INTO people VALUES("Alex","M");
> INSERT INTO people VALUES("Jane","F");
> INSERT INTO people VALUES("John","M");
>
> How to construct a query which returns coalesced sex but individual names,
> such as "F: Alex, Jane. M: Alex, John."?

SELECT sex || ': ' || group_concat(name, ", ")  FROM people GROUP BY sex;

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] 2D query

Hick Gunter
In reply to this post by Valentin Davydov-2
select group_concat(members,'. ')||'.' from (select sex||': '||group_concat(name) as members from people group by sex);
group_concat(members,'. ')||'.'
-------------------------------
F: Alex,Jane. M: Alex,John.


-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Valentin Davydov
Gesendet: Mittwoch, 13. Dezember 2017 14:57
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] 2D query

Hi, all!

Given the following table:

CREATE TABLE people(name,sex);
INSERT INTO people VALUES("Alex","F");
INSERT INTO people VALUES("Alex","M");
INSERT INTO people VALUES("Jane","F");
INSERT INTO people VALUES("John","M");

How to construct a query which returns coalesced sex but individual names, such as "F: Alex, Jane. M: Alex, John."?

Sincerely,
Valentin Davydov.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Seasonal syntax

Warren Young
In reply to this post by Simon Slavin-3
On Dec 12, 2017, at 10:24 AM, Simon Slavin <[hidden email]> wrote:
>
>      Santa Clause: SELECT name,hobbies,address FROM people WHERE behaviour=‘nice’

I think you mean

    SELECT name,address
    CASE behaviour
      WHEN ‘nice' THEN
        hobbies
      ELSE
        'coal'
      END
    FROM people

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Seasonal syntax

Rowan Worth-2
On 14 December 2017 at 01:19, Warren Young <[hidden email]> wrote:

> On Dec 12, 2017, at 10:24 AM, Simon Slavin <[hidden email]> wrote:
> >
> >      Santa Clause: SELECT name,hobbies,address FROM people WHERE
> behaviour=‘nice’
>
> I think you mean
>
>     SELECT name,address
>     CASE behaviour
>       WHEN ‘nice' THEN
>         hobbies
>       ELSE
>         'coal'
>       END
>     FROM people
>

Surely "hobbies" should read something like:

    (SELECT gift FROM ideas WHERE ideas.hobby IN (SELECT value FROM
json_each(hobbies)) ORDER BY random() LIMIT 1)

-Rowan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Seasonal syntax

Peter da Silva
SELECT name, address
CASE behaviour
  WHEN 'nice' THEN SELECT toy FROM stocking_stuffers ORDER BY random() LIMIT 1
  WHEN 'naughty' THEN 'coal'
  ELSE phnglui mgwlnafth cthulhu....
  END

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users