CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 1216

SQL Server Top 3 in Select with Joins

I am trying to extract the Top 3 of each group in a select with multiple joins. The Top 3 should group by the email address and symbol_short field (stocks). In the data I have 1 record where the symbol_short is Bitcoin, 4 records with XBY, and 1 with XVG for j@doe and 4 XBY for s@doe.

It looks something like this (other columns removed for simplicity)

email      | symbol_short
-----------+-------------
 [email protected] | Bitcoin     
 [email protected] | XBY
 [email protected] | XBY
 [email protected] | XBY    
 [email protected] | XBY    
 [email protected] | XVG
 [email protected] | XBY
 [email protected] | XBY    
 [email protected] | XBY    
 [email protected] | XBY

It should return the Top 3 of symbol_short grouped by the email address like this:

 email     | symbol_short
-----------+-------------
 [email protected] | Bitcoin     
 [email protected] | XBY
 [email protected] | XBY
 [email protected] | XBY   
 [email protected] | XVG
 [email protected] | XBY
 [email protected] | XBY    
 [email protected] | XBY

This is the select which currently returns all rows.

SELECT        
c_connection.email, c_alert.exchange, c_alert.symbol_short, c_alert.price_usd, 
c_history.alert_price, c_history.current_price, c_history.dynamic,
c_history.positive_change, c_history.negative_change, c_history.created, c_connection.username
FROM 
c_alert INNER JOIN c_history ON c_alert.id = c_history.id_alert INNER JOIN c_connection ON c_alert.login_key = c_connection.login_key
ORDER BY c_connection.email, c_alert.symbol_short, c_history.created DESC

Is it possible to get the Top 3 for the email, symbol_short group? 2 field solution would be great, potentially I would need all the 'select' fields as shown above in the result. Thanks!

Final query, tweaked based on GordonLinoff's answer:

with t as (

      SELECT        
c_connection.email, c_alert.exchange, c_alert.symbol_short, c_alert.price_usd, 
c_history.alert_price, c_history.current_price, c_history.dynamic,
c_history.positive_change, c_history.negative_change, c_history.created, c_connection.username
FROM 
c_alert INNER JOIN c_history ON c_alert.id = c_history.id_alert INNER JOIN c_connection ON c_alert.login_key = c_connection.login_key
-- ORDER BY c_connection.email, c_alert.symbol_short, c_history.created DESC

     )
select t.*
from (select t.*, row_number() over (partition by t.email, t.symbol_short order by t.email) as seqnum
      from t
     ) t
where seqnum <= 3;

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Hmmm . . . I'm thinking that you want at most three of any symbol for a given email. If so:

with t as (
      <your query here>
     )
selecct t.*
from (select t.*, row_number() over (partition by email, symbol_short order by email) as seqnum
      from t
     ) t
where seqnum <= 3;

Upvotes: 4

Related Questions