I have a database with a table of cars, the table has a number of different columns. I need to output the content within that table ordered by the Make of each car, only three cars from each make need to be outputted along side the total from eachh row of car. I also need to have the output ordered in descending order accompanied by a column called Ranking that counts up from 1 to however many outputs there will be.
Below is a sample from my databse table
|Timestamp |Email |Name |Year|Make |Model |Car_ID|Judge_ID|Judge_Name|Racer_Turbo|Racer_Supercharged|Racer_Performance|Racer_Horsepower|Car_Overall|Engine_Modifications|Engine_Performance|Engine_Chrome|Engine_Detailing|Engine_Cleanliness|Body_Frame_Undercarriage|Body_Frame_Suspension|Body_Frame_Chrome|Body_Frame_Detailing|Body_Frame_Cleanliness|Mods_Paint|Mods_Body|Mods_Wrap|Mods_Rims|Mods_Interior|Mods_Other|Mods_ICE|Mods_Aftermarket|Mods_WIP|Mods_Overall|
|--------------|-------------------------|----------|----|--------|---------|------|--------|----------|-----------|------------------|-----------------|----------------|-----------|--------------------|------------------|-------------|----------------|------------------|------------------------|---------------------|-----------------|--------------------|----------------------|----------|---------|---------|---------|-------------|----------|--------|----------------|--------|------------|
|8/5/2018 14:10|[email protected] |Hernando |2015|Acura |TLX |48 |J04 |Bob |0 |0 |2 |2 |4 |4 |0 |2 |4 |4 |2 |4 |2 |2 |2 |2 |2 |0 |4 |4 |4 |6 |2 |0 |4 |
|8/5/2018 15:11|[email protected] |Noel |2015|Jeep |Wrangler |124 |J02 |Carl |0 |6 |4 |2 |4 |6 |6 |4 |4 |4 |6 |6 |6 |6 |6 |4 |6 |6 |6 |6 |6 |4 |6 |4 |6 |
|8/5/2018 17:10|[email protected] |Edan |2015|Lexus |Is250 |222 |J05 |Adrian |0 |0 |0 |0 |0 |0 |0 |0 |6 |6 |6 |0 |0 |6 |6 |6 |0 |0 |0 |0 |0 |0 |0 |0 |4 |
|8/5/2018 17:34|[email protected] |Hieronymus|1993|Honda |Civic eG |207 |J06 |Aaron |0 |0 |2 |2 |2 |2 |2 |2 |0 |4 |2 |2 |2 |2 |2 |2 |4 |2 |2 |0 |0 |0 |2 |2 |0 |
|8/5/2018 14:30|[email protected] |Nickolas |2016|Ford |Mystang |167 |J02 |Carl |0 |0 |2 |2 |0 |2 |2 |0 |0 |0 |0 |2 |0 |2 |2 |2 |0 |0 |2 |0 |0 |0 |0 |0 |2 |
|8/5/2018 16:12|[email protected] |Martin |2013|Hyundai |Gen coupe|159 |J04 |Bob |0 |0 |2 |0 |0 |0 |2 |0 |0 |0 |0 |2 |0 |2 |2 |0 |2 |0 |2 |0 |0 |0 |0 |0 |0 |
|8/5/2018 17:00|[email protected]|Aldridge |2009|Infiniti|G37 |20 |J06 |Aaron |2 |0 |2 |2 |0 |0 |2 |0 |0 |2 |2 |2 |2 |2 |2 |2 |2 |2 |4 |2 |2 |0 |2 |0 |2 |
|8/5/2018 16:11|[email protected] |Ambros |2009|Honda |Oddesy |178 |J06 |Aaron |2 |0 |2 |2 |2 |2 |2 |0 |4 |4 |2 |2 |2 |4 |4 |4 |2 |2 | |6 |4 |4 |6 |4 |6 |
|8/5/2018 17:29|[email protected]|Quincy |2012|Hyundai |Celoster |30 |J04 |Bob |0 |0 |2 |2 |2 |2 |2 |4 |6 |6 |4 |2 |4 |4 |6 |6 |4 |0 |2 |0 |0 |0 |2 |2 |4 |
The expected output is something like this below
|Ranking |Car_ID|Year |Make |Model |Total|
|--------|------|-------|------|-----------|-----|
|1 |48 |2015 |Acura |TLX |89 |
|2 |66 |2012 |Acura |MDX |75 |
|3 |101 |2022 |Acura |TLX |70 |
|4 |22 |2011 |Chevy |Camaro |112 |
|5 |40 |2015 |Chevy |Corvette |99 |
|6 |205 |2022 |Chevy |Corvette |66 |
|7 |111 |2006 |Ford |Mustang |94 |
|8 |97 |2003 |Ford |GT |88 |
|9 |71 |2008 |Ford |Fiesta ST |80 |
Here's the command I've been been able to put together which does something similar to what I need, but I can't figure out how to do the ranking column and order by descending from the total.
SELECT Car_ID, Year, Make, Model, Racer_Turbo Racer_Supercharged ... Mods_Overall FROM Carstable order by Make limit 3;
This query command only returned three results instead of all, I also can't figure out where to put the DESC keyword in the command in order to have them listed in descending order based on the total column or how to do the ranking column as well. Any ideas?
CodePudding user response：
Use a CTE which returns the column Total for each row and ROW_NUMBER() window function to pick the first 3 rows for each Make and to create the column Ranking:
WITH cte AS (
SELECT *,
Racer_Turbo Racer_Supercharged Racer_Performance Racer_Horsepower
Car_Overall
Engine_Modifications Engine_Performance Engine_Chrome Engine_Detailing Engine_Cleanliness
Body_Frame_Undercarriage Body_Frame_Suspension Body_Frame_Chrome Body_Frame_Detailing Body_Frame_Cleanliness
Mods_Paint Mods_Body Mods_Wrap Mods_Rims Mods_Interior Mods_Other Mods_ICE Mods_Aftermarket Mods_WIP Mods_Overall Total
FROM carstable
)
SELECT ROW_NUMBER() OVER (ORDER BY Make, Total DESC) Ranking,
Car_ID, Year, Make, Model, Total
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Make ORDER BY Total) rn FROM cte)
WHERE rn <= 3
ORDER BY Make, Total DESC;
See the demo.