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?
