Re: Novice SQL question - Mailing list pgsql-novice

Thanks! I just hadn't seen that syntax before, but it makes 100% sense,
and matches the databases and tables.

-----Original Message-----
From: James Keener <jim@jimkeener.com>
To: drexl@little-beak.com
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Novice SQL question
Date: Tue, 21 May 2019 13:10:17 -0400

p is the alias for the petitions table (in the FROM clause). p.slug is
the slug field on the table p. (fwiw the same table can appear with
multiple references. in such a case, the table is treated as distinct
tables. That's not the case here, but that's why I said "on the tale p"
not "on the table petitions".)

In MySQL p.slug could be the slug table in the p database if the name
of a table is required in the syntax. If a field is required in the
syntax, it is always the table.field. PostgreSQL is the same, except
it's the p.slug is the slug table in the p schema (in the current
database).

On Tue, May 21, 2019 at 1:03 PM Dr. Drexl Spivey <drexl@little-beak.com
> wrote:
> Hello,
> 
> I don't know if this is the place for such inquiries, but I figured I
> would try. Worse case, someone could point me in the right direction.
> 
> I got an assignment to determine the results of the following query. 
> I
> can figure out most of it, but one or two questions remain.
> 
> WITH user_signatures AS (
>         SELECT
>                 petition_id,
>                 email,
>                 RANK() OVER (PARTITION BY email ORDER BY created_at)
> AS
>                 petition_rank,
>                 COUNT(*) OVER (PARTITION BY email) AS petition_count
>                 FROM signatures
> ), first_user_signatures AS (
>         SELECT *
>         FROM user_signatures
>         WHERE petition_rank = 1
> )
> SELECT
>         p.id,
>         p.slug,
>         SUM(first_user_signatures.petition_count) AS activity_points
> FROM petitions p INNER JOIN first_user_signatures
> ON first_user_signatures.petition_id = p.id
> GROUP BY p.id, p.slug
> ORDER BY activity_points DESC
> LIMIT 10;
> 
> Though I never used the "WITH" clause, I've learned it's used to
> create
> CTE (temporary tables). Most of the clauses and results are self
> explanatory, but I am unclear on the last subquery:
> 
> what exactly is the p.id, p.slug, and later the inter-join between
> the
> petitions p.
> 
> My understanding, and I guess it's possible, "p" would be a database,
> and "id, and slug" are tables within database p. But, then, my
> understanding of the FROM clause, "petitions p" doesn't jive??
> 
> My only other guess is that the "p" is just some reference to the
> "petitions" database??
> 
> 
>