Calculate show points in SQL

Transact-SQL
1

Hi,

This query works now, but it needs to be more specific for the customer. I also looked to see if I can adjust the data in the (PHP) array, but I prefer to pull it out of the query so that sorting continues to work properly. I've been stuck on this part for a while now. SQL statements are not my specialty.

Now it shows the total points well, but what I want to achieve is the following:

  • It must count the points of the national show (type 1) first, since an animal participates in a national show a maximum of 1 time per year.
  • Then it must select the 3 highest points and then stop counting (LIMIT).

So when an animal has not yet participated in a national show, it takes the 3 highest scores and if one has participated, it still takes the 3 highest scores, but the national one must be above that.

(show_participants.participant_category = "1" is now static, but this is for testing. There are 10 categories)

Hope it's clear, thanks a lot in advance.

SELECT Sum(participant_match_points) AS participant_total_points,
       shows.show_type,
       show_types.type_name,
       animals.born,
       animals.registrationnumber,
       owners.ownernumber
FROM   show_participants
       INNER JOIN animals
               ON animals.registrationnumber =
                  show_participants.participant_animal
       INNER JOIN show_categories
               ON show_participants.participant_category =
                  show_categories.category_id
       INNER JOIN owners
               ON animals.registrationnumber = owners.registrationnumber
       INNER JOIN shows
               ON show_participants.participant_show = shows.show_id
       INNER JOIN show_types
               ON show_types.type_id = shows.show_type
WHERE  show_participants.participant_category = "1"
GROUP  BY show_participants.participant_animal
ORDER  BY participant_total_points DESC

Screenshot 2024-07-31 at 20.23.46
Screenshot 2024-07-31 at 20.23.461425×1738 369 KB

2 
Hi 
. hope this helps 

WITH RankedPoints AS (
    SELECT 
        Sum(participant_match_points) AS participant_total_points,
        shows.show_type,
        show_types.type_name,
        animals.born,
        animals.registrationnumber,
        owners.ownernumber,
        ROW_NUMBER() OVER (PARTITION BY show_participants.participant_animal ORDER BY 
            CASE WHEN shows.show_type = 1 THEN 1 ELSE 2 END, 
            participant_total_points DESC) AS rn
    FROM 
        show_participants
        INNER JOIN animals ON animals.registrationnumber = show_participants.participant_animal
        INNER JOIN show_categories ON show_participants.participant_category = show_categories.category_id
        INNER JOIN owners ON animals.registrationnumber = owners.registrationnumber
        INNER JOIN shows ON show_participants.participant_show = shows.show_id
        INNER JOIN show_types ON show_types.type_id = shows.show_type
    WHERE 
        show_participants.participant_category = '1'
    GROUP BY 
        show_participants.participant_animal, shows.show_type, show_types.type_name, animals.born, animals.registrationnumber, owners.ownernumber
)

SELECT 
    participant_total_points,
    show_type,
    type_name,
    born,
    registrationnumber,
    ownernumber
FROM 
    RankedPoints
WHERE 
    rn <= 3
ORDER BY 
    participant_total_points DESC;