From: | "Dr(dot) Drexl Spivey" <drexl(at)little-beak(dot)com> |
---|---|
To: | pgsql-novice <pgsql-novice(at)lists(dot)postgresql(dot)org> |
Cc: | Dimitri <dimitri(at)shackletonpacific(dot)com>, James Keener <jim(at)jimkeener(dot)com> |
Subject: | Re: Novice SQL question |
Date: | 2019-05-21 17:15:26 |
Message-ID: | 1558458926.9205.8.camel@little-beak.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)jimkeener(dot)com>
To: drexl(at)little-beak(dot)com
Cc: pgsql-novice(at)lists(dot)postgresql(dot)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(at)little-beak(dot)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??
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Dr. Drexl Spivey | 2019-05-22 08:16:59 | SQL help? |
Previous Message | James Keener | 2019-05-21 17:10:17 | Re: Novice SQL question |