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: | 2019-05-21 17:10:17 |
Message-ID: | CAG8g3txEX5+0tPUOugLQdg14WqK0RHKqOyM196YL85OMj=u0qg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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-21 17:15:26 | Re: Novice SQL question |
Previous Message | Dr. Drexl Spivey | 2019-05-21 17:03:14 | Novice SQL question |