SQL按Formation对NumberOfPlays和FourthDownConversions降序排列问题

Hey there! Let's figure out why your SQL isn't sorting both columns in descending order as you want. The issue is almost certainly in how you're defining your ORDER BY clause—when you want multiple columns to sort descending, you need to explicitly specify DESC for each one, otherwise only the first column gets the descending treatment, and the rest default to ascending.

First off, I'm assuming you're grouping your results by Form (since you're calculating metrics per formation). If you haven't added a GROUP BY Form clause yet, that's a must to get aggregated values for each unique formation.

Here's how to adjust your SQL to sort both NumberOfPlays and FourthDownConversionRate in descending order:

SELECT 
    Form, 
    -- Pro tip: Multiply by 1.0 to avoid integer division (common in many SQL dialects)
    Down*1.0/Dist AS AverageDistance, 
    Play*1.0/Down AS NumberOfPlays, 
    Gain*1.0/Down AS FourthDownConversionRate
FROM 
    YourTableName  -- Replace this with your actual table name
GROUP BY 
    Form  -- Group results to calculate metrics per formation
ORDER BY 
    NumberOfPlays DESC,  -- First sort by play count descending
    FourthDownConversionRate DESC;  -- Then sort by conversion rate descending (when play counts match)

A few key details to clarify:

• If you previously wrote something like ORDER BY NumberOfPlays, FourthDownConversionRate DESC, only the conversion rate would sort descending—NumberOfPlays would default to ascending. That's likely what's causing your current issue!
• The *1.0 trick fixes situations where dividing two integers truncates to an integer instead of returning a decimal (e.g., 5/2 becomes 2 instead of 2.5). If your SQL dialect handles floating-point division automatically, you can skip this, but it's a safe, universal practice.
• If you want to prioritize conversion rate over play count, just swap the order in the ORDER BY clause (e.g., FourthDownConversionRate DESC, NumberOfPlays DESC).

That should get you the sorted, grouped results you're aiming for!

内容的提问来源于stack exchange，提问作者user9505804