CODEWITHSUNDEEP
mysqlsql
Adas
Adas

Reputation: 414

MySQL Order By not consistent

I have a code in sql that tries to grab portfolios, share price of some trades, in the numerical order by the trades.id field.

The problem is both values in the code below has to return in numerical order in order to calculate some type of allocation based on the weight.

However,

The return order of portfolios relative to trades.id field is the following

0,1,2,3,4,5,6...

Return order of the "Weights" derived from "shares" is the following

1,2,3,4,5,6....0

They should both be in the order of 0,1,2,3,4,5,6

PS. Not my code but i have to fix it in order for the application to work properly

SELECT 
    'O' AS Hint,
    GROUP_CONCAT(trades.id
        ORDER BY trades.id) AS ID,
    GROUP_CONCAT( (trades.Portfolio)
        ORDER BY trades.id) AS Portfolio,
    Ticker,
    Direction,
    FORMAT(SUM(Shares), 0) AS 'Shares',
    FORMAT(SUM(Shares), 0) AS 'Original Shares',
    Price,
    Broker AS Broker1,
    Commission,
    GROUP_CONCAT(FORMAT

    (trades.Shares/ 
    (SELECT SUM(Shares) FROM db.Trades WHERE id IN ('102370' , '102371','102372','102373','102374','102375','102376','102377','102380','102400')),12)) AS Weights,


    Issuer
FROM
    db.Trades
WHERE
    id IN ('102370' , '102371',
        '102372',
        '102373',
        '102374',
        '102375',
        '102376',
        '102377',
        '102380',
        '102400')
        AND is_deleted = '0'

GROUP BY Ticker , Direction , Price , Commission
ORDER BY trades.id

Upvotes: 0

Views: 245

Answers (2)

Adas
Adas

Reputation: 414

I just fixed the bug.. The code was missing order by trades.id in the group_concat method that wraps the shares.

(trades.Shares/ 
       (SELECT SUM(Shares) 
             FROM db.Trades WHERE id IN 
 ('102370''102371','102372','102373','102374','102375','102376','102377','102380','102400')),12) 
 order by trades.id) AS Weights   -- <- here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You query has this structure:

SELECT . . .
FROM . . .
GROUP BY Ticker , Direction , Price , Commission
ORDER BY trades.id

Note the GROUP BY. After the GROUP BY, the only unaggregated columns are the ones listed: Ticker, Direction, Price, and Commission.

Important question: Does this list include trades.id?

In most databases (and the more recent versions of MySQL) you would get an error. You can fix this using an aggregation function, say:

ORDER BY MIN(trades.id)

Upvotes: 0

Related Questions