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?