Get the column order of a query

I have a table with two columns [id, value] both numeric.

In this example:

[ id, value ]
[ 1,  6     ]
[ 2,  4     ]
[ 3,  10    ]
[ 4,  2     ]
[ 5,  7     ]
[ 6,  3     ]

For a given id I'd like to retrieve the top 3 id's (those with highest value), their top position and if the given id is not in the t开发者_如何学Cop 3, also get its position, id and value:

Example 1: ask_id = 5 Return:

[ position, id, value ]
[ 1,        3,  10    ]
[ 2,        5,  7     ]
[ 3,        1,  6     ]

Example 2: ask_id = 4. Return:

[ position, id, value ]
[ 1,        3,  10    ]
[ 2,        5,  7     ]
[ 3,        1,  6     ]
[ 6,        4,  2     ]

So the important points are:

• How to get for the position column?
• How to get the additional row if possible (anyway there's no problem if I need two queries)?

---

select t2.pos, t1.id, t1.value 
from test as t1
inner join
(select id, value, @pos:=if(@pos is null, 0, @pos)+1 as pos 
 from test order by value desc) as t2
on t1.id=t2.id
where t2.pos<=3 or t2.id={$ask_id}
order by t2.pos;

---

Basically, the idea is like this:

1. Rank the rows by value.

2. Retrieve rows where at least one of the following is true:

   • position BETWEEN 1 AND 3

   • id = @given_id

These posts give examples of how you could substitute ranking functions (at least the most fundamental of them, ROW_NUMBER()) in MySQL:

• ROW_NUMBER() in MySQL

• MSSQL Row_Number() over(order by) in MySql

This method should be used with caution, though, as this article explains.

That said, one possible implementation of the above steps might look like this:

SET @pos = 0;

SELECT
  position,
  id,
  value
FROM (
  SELECT
    id,
    value,
    @pos := @pos + 1 AS position
  FROM atable
  ORDER BY value DESC
) s
WHERE position BETWEEN 1 AND 3
   OR id = @given_id
ORDER BY position

---

Tested in MySQL to retrieve the top 3 id's (those with highest value) with position in ascending order.

set @num = 0;
SELECT @num := @num + 1 as position_sequence,id,value FROM tablename
ORDER BY value desc
limit 3;

---

I've not (yet) tested the selected answer in MySQL on the interesting cases where there are ties in the top three places, but I have tested this code in Informix on those cases, and it produces the answer I think should be produced.

Assuming that the table is called leader_board:

CREATE TABLE leader_board(id INTEGER NOT NULL PRIMARY KEY, value INTEGER NOT NULL);
INSERT INTO leader_board(id, value) VALUES(1, 6);
INSERT INTO leader_board(id, value) VALUES(2, 4);
INSERT INTO leader_board(id, value) VALUES(3, 10);
INSERT INTO leader_board(id, value) VALUES(4, 2);
INSERT INTO leader_board(id, value) VALUES(5, 7);
INSERT INTO leader_board(id, value) VALUES(6, 3);

This query works on the data shown, assuming that the special ID is 4:

SELECT b.position - c.tied + 1 AS standing, a.id, a.value
  FROM leader_board AS a
  JOIN (SELECT COUNT(*) AS position, d.id
          FROM leader_board AS d
          JOIN leader_board AS e ON (d.value <= e.value)
         GROUP BY d.id
       ) AS b
    ON a.id = b.id
  JOIN (SELECT COUNT(*) AS tied, f.id
          FROM leader_board AS f
          JOIN leader_board AS g ON (f.value = g.value)
         GROUP BY f.id
       ) AS c 
    ON a.id = c.id
 WHERE (a.id = 4 OR (b.position - c.tied + 1) <= 3) -- Special ID = 4; Top N = 3
 ORDER BY position, a.id;

Output on original data:

standing        id      value

      1          3         10
      2          5          7
      3          1          6
      6          4          2

Explanation

The two sub-queries are closely related, but they produce different answers. At one time, I used two temporary tables to hold those results. In particular, the first sub-query (AS b) produces a position, but when there are ties, the position is the lowest rather than the highest of the tied positions. That is, given:

 ID     Value
  1        10
  2         7
  3         7
  4         7

The outputs will be:

Position   ID
  1         1
  4         2
  4         3
  4         4

However, we would like to count them as:

Position   ID
  1         1
  2         2
  2         3
  2         4

So, the corrected position is the original position minus the number of tied values (3 for ID ∈ { 2, 3, 4 }, 1 for ID 1) plus 1. The second sub-query returns the number of tied values for each ID. There might be a neater way to do that calculation, but I'm not sure what it is at the moment.

Special cases

However, the code should demonstrate that it handles the cases where:

1. There are 2 or more ID values with the same top value.
2. There are 2 or more ID values with the same second highest top score (but the top one is unique).
3. There are 2 or more ID values with the same third highest top score (but the top two are unique).

To save rewriting the query each time, I converted it into an Informix-style stored procedure which take both the Special ID and the Top N (defaulting to 3) values that should be displayed and made them into parameters of the procedure. (Yes, the notation in the RETURNING clause is weird.)

CREATE PROCEDURE leader_board_standings(extra_id INTEGER, top_n INTEGER DEFAULT 3)
    RETURNING INTEGER AS standing, INTEGER AS id, INTEGER AS value;
    DEFINE standing, id, value INTEGER;
    FOREACH SELECT b.position - c.tied + 1 AS standing, a.id, a.value
              INTO standing, id, value
              FROM leader_board AS a
              JOIN (SELECT COUNT(*) AS position, d.id
                      FROM leader_board AS d
                      JOIN leader_board AS e ON (d.value <= e.value)
                     GROUP BY d.id
                   ) AS b
                ON a.id = b.id
              JOIN (SELECT COUNT(*) AS tied, f.id
                      FROM leader_board AS f
                      JOIN leader_board AS g ON (f.value = g.value)
                     GROUP BY f.id
                   ) AS c
                ON a.id = c.id
             WHERE (a.id = extra_id OR (b.position - c.tied + 1) <= top_n)
             ORDER BY position, a.id
        RETURN standing, id, value WITH RESUME;
    END FOREACH;
END PROCEDURE;

This can be invoked to produce the same result as before:

EXECUTE PROCEDURE leader_board_standings(4);

To illustrate the various cases outlined above, add and remove extra rows:

EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      3          1          6
      6          4          2

INSERT INTO leader_board(id, value) VALUES(10, 10);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      1         10         10
      3          5          7
      7          4          2

INSERT INTO leader_board(id, value) VALUES(11, 10);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      1         10         10
      1         11         10
      8          4          2

INSERT INTO leader_board(id, value) VALUES(12, 10);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      1         10         10
      1         11         10
      1         12         10
      9          4          2

DELETE FROM leader_board WHERE id IN (10, 11, 12);
EXECUTE PROCEDURE leader_board_standings(6, 4);     -- Special ID 6; Top 4

      1          3         10
      2          5          7
      3          1          6
      4          2          4
      5          6          3

INSERT INTO leader_board(id, value) VALUES(7, 7);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      2          7          7
      7          4          2

INSERT INTO leader_board(id, value) VALUES(13, 7);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      2          7          7
      2         13          7
      8          4          2

INSERT INTO leader_board(id, value) VALUES(14, 7);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      2          7          7
      2         13          7
      2         14          7
      9          4          2

DELETE FROM leader_board WHERE id IN(7, 13, 14);
INSERT INTO leader_board(id, value) VALUES(8, 6);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      3          1          6
      3          8          6
      7          4          2

INSERT INTO leader_board(id, value) VALUES(9, 6);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      3          1          6
      3          8          6
      3          9          6
      8          4          2

INSERT INTO leader_board(id, value) VALUES(15, 6);
EXECUTE PROCEDURE leader_board_standings(4);

      1          3         10
      2          5          7
      3          1          6
      3          8          6
      3          9          6
      3         15          6
      9          4          2

EXECUTE PROCEDURE leader_board_standings(3);  -- Special ID 3 appears in top 3

      1          3         10
      2          5          7
      3          1          6

That all looks correct to me.