CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bygreatest-n-per-group
ColorfulWind
ColorfulWind

Reputation: 87

How to find the min, where TSQL groups by

I have found the first transaction (min), but when I add the column 'Winners', I get a row for their first win and a row for their first loss. I need only the first row, including whether they won or lost. I have tried aggregating the winners column to no avail. I would prefer not to sub-query if possible. Thanks in advance for checking this out.

SELECT 
MIN(dbo.ADT.Time) AS FirstShowWager, 
dbo.AD.Account,  dbo.AD.FirstName, 
dbo.AD.LastName, dbo.ADW.Winners  

FROM     
dbo.BLAH
WHERE       
(dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-12
00:00:00', 102)) AND (dbo.ADW.Pool = N'shw') 

GROUP BY
dbo.AD.Account, 
dbo.AD.FirstName,
dbo.AD.LastName,
dbo.AD.RunDate, 
dbo.ADW.Winners 

ORDER BY 
dbo.AD.Account

Upvotes: 1

Views: 88

Answers (5)

paparazzo
paparazzo

Reputation: 45096

select sorted.* 
from 
(
    SELECT dbo.ADT.Time AS FirstShowWager, 
           dbo.AD.Account,  dbo.AD.FirstName, 
           dbo.AD.LastName, dbo.ADW.Winners,  
           ROW_NUMBER ( ) OVER (partition by dbo.AD.Account, 
                                             dbo.AD.FirstName,
                                             dbo.AD.LastName,
                                             dbo.AD.RunDate
                                    order by dbo.ADT.Time) as rowNum
      FROM dbo.AD
     WHERE dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-1200:00:00', 102) 
       AND dbo.ADW.Pool = N'shw'
) as sorted 
where rowNum = 1

ROW_NUMBER

Upvotes: 1

Steven
Steven

Reputation: 911

You can find the MIN in an inner query and then join it to the ADW table on by the ID to get if they are a winner.

SELECT b.*, ADW.winner 
FROM dbo.ADW ADW INNER JOIN (SELECT MIN(ADT.RunTime) AS FirstShowWager, 
                             AD.Account,  AD.FirstName, 
                             AD.Lastnamne, AD.ADID
                             FROM dbo.AD AD INNER JOIN dbo.ADT ADT AD.adid = ADT.ADID 
                             GROUP BY AD.Account, AD.Firstname, AD.Lastnamne, AD.ADID) b
    ON ADW.ADID = b.ADID

Assumptions: There is a foreign key between From the ADT to the AD table. From the ADW to the AD table.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

Usually this is done with a derived table selecting the record you want then joining back to the orginal table on all the group by fields.

Upvotes: 0

artm
artm

Reputation: 8584

It sounds like you don't care about the value of winners column, by grouping on winners you'd get multiple rows, one for null and others for non-null values. If you don't care about the amount they've won but just simply if they've won or lost, you can do something like this,

SELECT 
MIN(dbo.ADT.Time) AS FirstShowWager, 
dbo.AD.Account,  dbo.AD.FirstName, 
dbo.AD.LastName, CASE WHEN dbo.ADW.Winners IS NULL THEN 0 ELSE 1 END

FROM     
dbo.BLAH
WHERE       
(dbo.ADT.RunDate = CONVERT(DATETIME, '2014-04-12
00:00:00', 102)) AND (dbo.ADW.Pool = N'shw') 

GROUP BY
dbo.AD.Account, 
dbo.AD.FirstName,
dbo.AD.LastName,
dbo.AD.RunDate, 
dbo.ADW.Winners 

ORDER BY 
dbo.AD.Account

Upvotes: 1

radar
radar

Reputation: 13425

Add this case statement instead of winners column in the select statement and group by

 Case ( winners is NULL then 'Lose' else 'Win' end )

Upvotes: 0

Related Questions