MySQL Join Query Position from Order-CodePudding

I am looking for a solution to get a row based on certain parameters but then also append that rows position from the ordered query.

So we look for a row like this:

SELECT * 
FROM `table` 
WHERE `SomeName`='UniqueName' 
ORDER BY `SomeFloat` DESC

This will get all of the rows I require, but then I'd also like to JOIN the position that this row exists in from that query, as if there are 3 records above that, which SomeFloat is higher than I'd like 4 to be returned, is this possible or do I simply need to run a second query?

My second query currently:

SELECT COUNT(*) 
FROM `table` 
WHERE `SomeFloat` > ( SELECT `SomeFloat` 
                      FROM `table` 
                      WHERE `SomeFloat` = 1635
                     )

So I just want to JOIN the second query as a final column for the initial query to save processing (unless this is actually negligible?) Of course 1635 is just an example value.

Thanks in advance!

EDIT:

For example:

The first command would search this table, and order the result by SomeFloat

UniqueID   UniqueName   SomeFloat   SomeText
       1          Bob        1.02      Lorem
       2         John       12.41      Ipsum
       3        Sarah        7.19     Lipsum
       4      Michael      123.45     Dorelo

Assuming we searched for John the second query would result in 1, since there is only 1 result higher (Michael's). If we searched for Bob then the result here would be 3 since the other 3 are higher.

The end result I would like is for a single query to return this, where RowsAbove would be generated by the second query.

UniqueID   UniqueName   SomeFloat   SomeText    RowsAbove
       2         John       12.41      Ipsum            1

CodePudding user response：

using a Common Table Expression (CTE) It's like an inline view but pulled out of inline for readability think of it as "Step 1" before your main query that stages data the way you need, in this case adding a row_number.
we generate a dataset CTE, which is all your data assigning a row number ordered by float descending. We do this so everything is assigned a sequential number before any records are removed via a where clause.
then we query the CTE for the unique name and it returns the RowNumber -1 as "position" or "rows above" or whatever it needs to be named.
since we assigned RowNumber to each record in descending order RN-1 would always be the number of rows with a higher float than the unique name selected.

UNTESTED:

WITH CTE AS (SELECT UniqueID, UniqueName,   SomeFloat,   SomeText
                  , row_number() over (PARTITION BY null ORDER BY SomeFloat DESC) as RN  
--or              , row_number() over (ORDER BY SomeFloat DESC) as RN  

             FROM table A)

SELECT UniqueID, UniqueName, SomeFloat, SomeText, RN-1 as Position
FROM CTE
WHERE UniqueName = 'John'

The CTE should give us this result:

UniqueID   UniqueName   SomeFloat   SomeText RN
       4      Michael      123.45     Dorelo 1
       2         John       12.41      Ipsum 2
       3        Sarah        7.19     Lipsum 3
       1          Bob        1.02      Lorem 4

and when we limit by John and subtract 1 from RN we get

       2         John       12.41      Ipsum 1