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
This query has no FROM clause and "TRUE" in double quotes would be considered a column reference...in short it is bogus. It helps considerably to provide a self-contained minimal example with some data (say to compute top 2...) and a working attempt or even a manual expected result.
I have no idea why "joined" is important.
The rank() window function is much more reliable when the partition contents are ordered so that assigning the rank is based off of some measure and not just random. It is also unlikely to be helpful for this query.
You may find the filter aggregate expression to be helpful though.
The count requires nothing more than a simple GROUP BY based query; you can do as Saurabh did an inline a scalar subselect for the name or take the grouped result and join it back to the petition table.
