CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user987723
user987723

Reputation: 965

SQL Group but only show top record for specific column

SELECT dbo.dPlaysPerDay.fkPlayerId AS Expr1, 
       sum(dbo.dPlaysPerDay.plays), 
       dbo.dWinners.pkWinnerId AS Expr2, 
       dbo.tbl_kissImages.kissImg AS Expr3, 
       dbo.dWinners.email AS Expr4, 
       dbo.tbl_kissImages.email
FROM dbo.dPlaysPerDay 
INNER JOIN dbo.dWinners ON dbo.dPlaysPerDay.fkPlayerId = dbo.dWinners.pkWinnerId 
INNER JOIN dbo.tbl_kissImages ON dbo.dWinners.email = dbo.tbl_kissImages.email
GROUP BY  dWinners.email, 
          dPlaysPerDay.fkPlayerId,
          dWinners.pkWinnerId, 
          tbl_kissImages.kissImg, 
          tbl_kissImages.email
ORDER BY dWinners.email

I have the above sql which returns a list like this:

54938|14|54938|output/15124.png|156|[email protected]
54938|4|54938|output/15103.png|156|[email protected]
54939|10|54939|output/15104.png|156|[email protected]
54939|9|54939|output/15124.png|156|[email protected]
54940|10|54940|output/15114.png|156|[email protected]
54940|9|54940|output/15109.png|156|[email protected]

I need to group this result by email adding up all of the plays (second column) for that email and only displaying the kiss image from the first record for that email address.

Thanks for any help in advance

Upvotes: 0

Views: 265

Answers (1)

xQbert
xQbert

Reputation: 35343

The following assumes by 1st you mean the earliest number of .png for a given email/player.

SELECT dbo.dPlaysPerDay.fkPlayerId AS Expr1, 
  sum(dbo.dPlaysPerDay.plays), 
  dbo.dWinners.pkWinnerId AS Expr2, 
  min(dbo.tbl_kissImages.kissImg) AS Expr3, 
  dbo.dWinners.email AS Expr4, 
  dbo.tbl_kissImages.email
FROM dbo.dPlaysPerDay 
INNER JOIN dbo.dWinners 
  ON dbo.dPlaysPerDay.fkPlayerId = dbo.dWinners.pkWinnerId 
INNER JOIN dbo.tbl_kissImages 
  ON dbo.dWinners.email = dbo.tbl_kissImages.email
GROUP BY  dWinners.email, 
          dPlaysPerDay.fkPlayerId,
          dWinners.pkWinnerId, 
          tbl_kissImages.email
ORDER BY dWinners.email

Upvotes: 1

Related Questions