From: | "Dr(dot) Drexl Spivey" <drexl(at)little-beak(dot)com> |
---|---|
To: | pgsql-novice <pgsql-novice(at)lists(dot)postgresql(dot)org> |
Subject: | SQL help? |
Date: | 2019-05-22 08:16:59 |
Message-ID: | 1558513019.9205.10.camel@little-beak.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Trying to do something very easy, but can't quite figure out the logic.
Typing out the email, and see if I can't figure it out just typing it
out.
Have two tables:
petitions
id = (int)primary key
name = text description
and the following
signed_petitions
signed_id = references petitions.id (Foreign key)
joined = boolean
my goal is to list those petitions that have the most signatures, so I
have to get a count of joined=true, and list the names.
What confuses me on a fundamental level, is the usage of the Foreign
key, reference. But, here is my best attempt.
Ok, this is a lot harder than I thought, because the boolean is not
actually a number. So I guess I will have to use a rank?
SELECT
petitions.id
petitions.name
RANK() OVER (PARTITION BY signed_petitions.joined) AS total_signed
WHERE
signed_petitions.joined = "TRUE"
ORDER BY total_signed DESC
LIMIT 10
I am not sure how the rank() over (partition by... works), this is my
first attempt.
Would this give me the ten highest signed petitions?
From | Date | Subject | |
---|---|---|---|
Next Message | Saurabh Agrawal | 2019-05-22 08:30:55 | Re: SQL help? |
Previous Message | Dr. Drexl Spivey | 2019-05-21 17:15:26 | Re: Novice SQL question |