Understanding a nested SQL query to sort around 1.0
Status: Site Admin
Joined: 26 Sep 2003
Posts: 3762
Location: East Coast, West Coast? I know it's one of them.
Reply Quote
Thanks to damentz of liquorix kernel for explaining this code so clearly.

The goal was to sort a column of numbers based on their distance from 1.0000, with the furthest sorted top, the closest bottom, ie, 1.054 then 0.956 etc, ending at 1.010, 0.991 and so on.

:: Quote ::
basically first we're writing a query that selects the actual value, as it is and as the second field, we dynamically create the field by subtracting it by 1 and running abs() on it.

So now we have a temp table in memory.

when you wrap that query with from( ... ), you're querying this transient table as if it was real so now you have both the original value and the adjusted value for sorting so you sort on the adjusted value and since both field are on the same row, properly sorts the unchanged value.

Think of it as piping (ie: echo 'yes this is true' | wc -c )

That's when i realized what was going on, sub-queries on sub-queries is just like starting with data and piping it to another command that makes minor changes

:: Code ::
SELECT x.percent
    SELECT percent, ABS(percent - 1) AS percent_abs
   FROM my_table
) x
ORDER BY x.percent_abs DESC

While I'm still not convinced I could generate one of these on my own, this explanation goes a long way towards understanding how they work, why, and how to use them.
Back to top
Display posts from previous:   

All times are GMT - 8 Hours