CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
bgeveritt
bgeveritt

Reputation: 303

SQL - Return Top 3 Records Per Field

I have the following subset of data:

Ticker A | Ticker B | Value 1 | Value 2
AAU        AAV        -0.49     0.98
AAU        AAWW       -0.68    -0.34
AAU        AAXJ       -0.82     0.54
AAU        AB         -0.49    -0.98
AAV        AAWW        0.74    -0.52
AAV        AAXJ        0.69     0.36
AAV        AB          0.27    -1.00
AAWW       AAXJ        0.89     0.61
AAWW       AB          0.62     0.54
AAXJ       AB          0.54    -0.34

Using MySQL, I need to return the top 3 (largest values) from both 'Value 1' and 'Value 2', based on a ticker. For example, I want to find the 3 largest 'Value 1' and 'Value 2' where the ticker is 'AAWW' (which can exist in either the Ticker A or Ticker B column), but I want to see the other ticker associated with it, per value. Based on the data above and that scenario, my desired recordset would be:

Ticker | Value 1 | Ticker | Value 2
AAV      0.89      AAXJ     0.61
AAXJ     0.72      AB       0.54
AB       0.62      AAU     -0.34

Ideally, I'd like to do this without a stored procedure. Is that possible?

Upvotes: 0

Views: 427

Answers (1)

manji
manji

Reputation: 47978

Try this (assuming you don't have an ID column, otherwise replace @rownum/rank by ID):

SELECT T1.SUB_Ticker, T1.VALUE1, T2.SUB_Ticker, T2.VALUE2
  FROM
(SELECT CASE WHEN TickerA = 'AAWW' THEN TickerA ELSE TickerB END Ticker,
        CASE WHEN TickerA = 'AAWW' THEN TickerB ELSE TickerA END SUB_Ticker,
        VALUE1,
        @rownum:=@rownum+1 ‘rank’
  FROM data_table d, (SELECT @rownum:=0) r
 WHERE TickerA = 'AAWW' OR TickerB = 'AAWW'
 ORDER BY VALUE1 DESC
 LIMIT 3) T1
JOIN
(SELECT CASE WHEN TickerA = 'AAWW' THEN TickerA ELSE TickerB END Ticker,
        CASE WHEN TickerA = 'AAWW' THEN TickerB ELSE TickerA END SUB_Ticker,
        VALUE2,
        @rownum:=@rownum+1 ‘rank’
  FROM data_table d, (SELECT @rownum:=0) r
 WHERE TickerA = 'AAWW' OR TickerB = 'AAWW'
 ORDER BY VALUE2 DESC
 LIMIT 3) T2 ON T1.rank = T2.rank

Upvotes: 1

Related Questions