SQL / SQLite Order By Tiered Based Values

Question

I have tried googling for how to do this but I don't know what to search for.

I want to select data from a database (sqlite) but I want to be able to give records higher priority than others based on a couple of conditions.

I have a table that holds my data, and when I select from the table I want to have the ability to ORDER BY a teamName field, and a percentSuccess field.

Normally, if I just do this: SELECT * FROM table ORDER BY teamName, percentSuccess DESC

I will get everything ordered first by teamName (in ASC alphabetical order) and the by perscentSuccess (in DESC order - so highest success rate first).

So to achieve prioritizing by teamName, I have to use a CASE in the ORDER BY:

SELECT * FROM table
ORDER BY
CASE teamName
WHEN 'charlie' THEN 0
WHEN 'alpha' THEN 1
WHEN 'beta' THEN 2
END

(this will order the results so that any records with teamName 'charlie' come first, before alpha and beta)

Now... it's clear that I am partial to team Charlie. But.... if team Alpha or Beta, have some players that have a high enough success rate compared to some slackers on team Charlie... I want to give those players on Alpha and Beta better priority than the slackers on Charlie.

For example: If a player is on team Charlie, and they have a success rate >= 0.75 (75%) then they should have top priority. If a player from Alpha or Beta has a success rate >= 0.75, they should outrank a player from Charlie that has a success rate <= 0.74 and >= 0.50. But if a player from Alpha or Beta has success <= 0.74 and >= 0.50 they rank below players from Charlie within the same success tier.

How can I do this, or what is this style of ordering called so I can search for it?

Answer 1

I think I worked this out after playing around for a few hours with it.

Seems like there wasn't a way to do a tiered ordering like I want, not that I can work out anyway, by just using the ORDER BY clause.

I have had to create a field called 'priority' in the SELECT statement, which has a CASE clause that defines the conditions that prioritizes a record.

So I ended up with something like:

SELECT
playerName,
teamname,
CAST(gamesWins as real) / CAST(gamesTotal as real) AS percentSuccessful
CASE
WHEN teamName = 'Charlie' AND CAST(gamesWins as real) / CAST(gamesTotal as real) >= 0.75 THEN 0
WHEN CAST(gamesWins as real) / CAST(gamesTotal as real) >= 0.75 THEN 1
WHEN teamName = 'Charlie' AND CAST(gamesWins as real) / CAST(gamesTotal as real) >= 0.50 THEN 2
WHEN CAST(gamesWins as real) / CAST(gamesTotal as real) >= 0.50 THEN 3
ELSE 4
END AS priority
FROM tableName
ORDER BY
priority ASC;

So all the priority tiering is done when we decide what value to give to field name 'priority' and not in the ORDER BY clause at all.

Also note: you can't use percentSuccessful in the priority CASE block as it is an alias. You can only use table field names in that stage of the query otherwise you get an error.

Answer 2

Tough work SQL is Nathan Stanley ,
Good to know you got it all sorted out 👍

Answer 3

Ipang I find it annoying to have to build a query in SQL to get the data in the formats I want, but if you put the time and effort into the SQL you don't have to do as much processing in your program code with the data, so the effort is worth it. it makes your program code simpler.

the really hard part is coming back 6 months later and trying to work out what your SQL query is doing!

Answer 4

Nathan Stanley
I know SQL is useful, but building a query to satisfy the need is indeed a tough work, and worth it, I didn't say otherwise as I recall.

Comments in SQL scripts can help with the come back confusion.