Re: SQL help?

On Wed, May 22, 2019 at 1:19 AM Dr. Drexl Spivey <drexl(at)little-beak(dot)com>
wrote:

> 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.

https://www.postgresql.org/docs/11/sql-expressions.html#SYNTAX-AGGREGATES

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.

David J.