Sortierungsproblem grosses SQL-Statement

[kaipi00 0]

Hallo, ich möchte erst nach Spalte "Count" und dann nach Spalte "avg"sortieren, so dass die höchsten Werte oben stehen, aber das Statemant reagiert nicht, egal ob ich ASC oder DESC angebe.
Muss ich evtuell eine Temp Tabelle erstellen, dann sortieren?
Vielen Dank für eure Tipps

 

SELECT ROW_NUMBER() OVER(ORDER BY "count" DESC) AS "Rang#",
"source"."count" AS "count", "source"."avg" AS "avg", "Cookie User"."first_name" AS "Vorname", LEFT("Cookie User"."last_name", 1) as "Nachname", "Cookie User"."state" AS "Bundesland", "Cookie Playerprofile"."challenge_code" AS "Talent Pool", EXTRACT(YEAR FROM "Cookie User"."birth_date") AS "Jahrgang", "Cookie User"."id" AS "id"
FROM (SELECT "public"."cookie_user"."id" AS "id", count(*) AS "count", avg("Skill Trainings Player Training Video"."dimension") AS "avg" FROM "public"."cookie_user"
LEFT JOIN "public"."skill_trainings_playertrainingvideo" "Skill Trainings Player Training Video" ON "public"."cookie_user"."id" = "Skill Trainings Player Training Video"."user_id" LEFT JOIN "public"."skill_trainings_playertraining" "Skill Trainings Player Training - Related Training" ON "Skill Trainings Player Training Video"."related_training_id" = "Skill Trainings Player Training - Related Training"."id" LEFT JOIN "public"."skill_trainings_ukixxleague" "Skill Trainings Uki Xx League - Active League" ON "public"."cookie_user"."active_league_id" = "Skill Trainings Uki Xx League - Active League"."id" LEFT JOIN "public"."skill_trainings_skilltraining" "Skill Trainings Skill Training - Training" ON "Skill Trainings Player Training - Related Training"."training_id" = "Skill Trainings Skill Training - Training"."id" LEFT JOIN "public"."skill_trainings_trainingsubcategory" "Skill Trainings Training Subcategory - Category" ON "Skill Trainings Skill Training - Training"."category_id" = "Skill Trainings Training Subcategory - Category"."id" LEFT JOIN "public"."skill_trainings_trainingmaincategory" "Skill Trainings Training Main Category - Main Category" ON "Skill Trainings Training Subcategory - Category"."main_category_id" = "Skill Trainings Training Main Category - Main Category"."id" RIGHT JOIN "public"."skill_trainings_playerround" "Skill Trainings Player Round - Training Round" ON "Skill Trainings Player Training - Related Training"."training_round_id" = "Skill Trainings Player Round - Training Round"."id"
WHERE "Skill Trainings Training Main Category - Main Category"."title" = 'JUNGLÖWEN CHALLENGE'
GROUP BY "public"."cookie_user"."id"
ORDER BY "count" DESC, "avg" ASC) "source" LEFT JOIN "public"."cookie_user" "Cookie User" ON "source"."id" = "Cookie User"."id" LEFT JOIN "public"."cookie_playerprofile" "Cookie Playerprofile" ON "Cookie User"."id" = "Cookie Playerprofile"."_user_id" 
WHERE (("Cookie User"."birth_date" >= timestamp with time zone '2008-01-01 00:00:00.000Z'
   AND "Cookie User"."birth_date" < timestamp with time zone '2011-12-31 00:00:00.000Z'))
   AND "Cookie Playerprofile"."challenge_code" = 'BTSV' 

 

[MDD 13]

Hallo

du machst die Sortierung an der falschen Stelle.

Die muss ganz zum Schluss kommen. Durch den letzten Join werden die Daten wieder über den Haufen geworfen.

Hallo

du machst die Sortierung an der falschen Stelle.

Die muss ganz zum Schluss kommen. Durch den letzten Join werden die Daten wieder über den Haufen geworfen.

Performancemäßig ist sowas vielleicht sogar kontraproduktiv da die Sortierung sicher nicht dem join Kriterium entspricht und der dann extra nochmal umsortiert werden muss um es zu verknüpfen 

Gruß mdd

bearbeitet 16. September 2022 von MDD