Re: SQL help?

SELECT signed_id, (SELECT name FROM petitions WHERE id=signed_id), count(
signed_petitions.id) *AS* number
FROM signed_petitions
WHERE joined
*GROUP BY* signed_id
*ORDER BY* number;

Have a pk in the signed_petitions table and count those?

On Wed, May 22, 2019 at 1:48 PM 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
>
>
>
> 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?
>
>
>