Search code examples
sqlvisual-foxpro

Is it possible for foxpro in sql statement to fill the winners_name column base on condition maximum score and id with different names

Is it possible for foxpro in sql statement to add and fill a winners_name column base on condition maximum score and id with different names.

I have created a sql statement but it was not supported by foxpro, is there other alternative to do this rather than using a loop (I like sql statement for faster result even in 50k row lines)

SELECT * , ;
(SELECT TOP 1 doc_name FROM Table1 as b1 WHERE ALLTRIM(b1.id) = a.id ORDER BY b1.score DESC, b1.id) as WINNERS_NAME ;
FROM Table1 as a

I have only 1 table, with columns [ name, id, score ]

A sample table would be like this

NAME   | ID   | SCORE |
BEN    | 101  | 5     |
KEN    | 101  | 2     |
ZEN    | 101  | 3     |
JEN    | 103  | 4     |
REN    | 103  | 3     |
LEN    | 102  | 5     |
PEN    | 102  | 4     |
ZEN    | 102  | 3     |

The result would be like this (winners_name is tag on ID)

NAME   | ID   | SCORE | WINNERS_NAME
BEN    | 101  | 5     | BEN
KEN    | 101  | 2     | BEN
ZEN    | 101  | 3     | BEN
JEN    | 103  | 4     | PEN
REN    | 103  | 3     | PEN
LEN    | 102  | 5     | LEN
PEN    | 103  | 5     | PEN
ZEN    | 102  | 3     | LEN
Solution

  • Try this approach:

    SELECT
    a.NAME,
    a.ID,
    a.SCORE,
    b.WINNERS_NAME
FROM Table1 a
INNER JOIN
(
    SELECT t1.ID, t1.NAME AS WINNERS_NAME
    FROM
    (
        SELECT ID, SCORE, MIN(NAME) AS NAME
        FROM Table1
        GROUP BY ID, SCORE
    ) t1
    INNER JOIN
    (
        SELECT ID, MAX(SCORE) AS MAX_SCORE
        FROM Table1
        GROUP BY ID
    ) t2
        ON t1.ID = t2.ID AND
           t1.SCORE = t2.MAX_SCORE 
) b
    ON a.ID = b.ID
ORDER BY
    a.ID;

    Follow the link below for a demo running in MySQL (though the syntax should still work on FoxPro):

    Demo