How to get first available value in a column (not MIN) using aggregate function on a NVARCHAR type column?

Question

I have a temp table (#TempTable) which looks like this:

AID         StartTime   EndTime     StartSID EndSID     Name

79F05D45    2013-07-02  2013-07-03  1226349  1227338    
79F05D45    2013-07-03  2013-07-03  1227381  1227901    
79F05D45    2013-07-03  2013-07-03  1233976  1233977    John Pringle
79F05D45    2013-07-03  2013-07-03  1234386  1234452    
79F05D45    2013-07-03  2013-07-03  1235138  1235147    
79F05D45    2013-07-03  2013-07-03  1235669  1235708    Mike Gordon
79F05D45    2013-07-03  2013-07-03  1235828  1239004    Jeff Smith

How do I use the Aggregate function on Name so that when I GROUP BY StartTime and EndTime I get John Pringle in the Name column (I tried using MIN(Name) but it gives me blank value and If I use MAX(Name) I get Jeff Smith )

Basically, my requirement is to get the first available value in column Name

Here is my Query:

SELECT
    TT.AID,
    MIN(StartTime) as StartTime,
    MAX(EndTime) as Endtime,
    MIN(StartSSID) as StartSID,
    MAX(EndSSID) AS EndSID,
    -- I tried MIN(Name) here but I get empty cell, MAX(Name) gives wrong result. Basically I want first available value in the Name column here (which is John Pringle).
    FROM
        #TempTable TT
    GROUP BY TT.AID, StartTime, EndTime

Result I get:

AID         StartTime   EndTime     StartSID EndSID     Name

79F05D45    2013-07-02  2013-07-03  1226349  1227338    
79F05D45    2013-07-03  2013-07-03  1227381  1239004

Result I am aiming for:

AID         StartTime   EndTime     StartSID EndSID     Name

79F05D45    2013-07-02  2013-07-03  1226349  1227338        
79F05D45    2013-07-03  2013-07-03  1227381  1239004    John Pringle

Thanks!

I was referring to string (NVARCHAR) type column. I just used generic term "text". — Learner, Jul 17 '13 at 21:15

t-clausen.dk · Accepted Answer · 2013-07-17T22:39:58.787

2

;WITH a as
(
SELECT
    AID,
    StartTime,
    EndTime,
    StartSSID,
    EndSSID,
    rn = row_number() over (partition by AID,cast(starttime as date) order by case WHEN name = '' or name is null then '20990101' else starttime end, starttime)
    FROM
        #TempTable TT
)
SELECT AID, StartTime, EndTime, StartSSID, EndSSID
WHERE rn = 1

edited Jul 17 '13 at 22:39

answered Jul 17 '13 at 20:59

t-clausen.dk

43,517
12
59
92

What about Group By TT.AID ? – Learner Jul 17 '13 at 21:25
@Learner I dont see any reason for needing that – t-clausen.dk Jul 17 '13 at 21:26
This is just sample data. I have multiple AIDs in the data. Sorry for confusion. I need Group By solution if possible. – Learner Jul 17 '13 at 21:29
I looked into this approach before posting the question. Here: http://stackoverflow.com/questions/773417/aggregate-sql-function-to-grab-only-the-first-from-each-group – Learner Jul 17 '13 at 21:30
@Learner added AID to the partition clause. That should resolve it – t-clausen.dk Jul 17 '13 at 21:32
Are you sure Partition By works with multiple columns? I get duplicate rows. – Learner Jul 17 '13 at 21:48
@Learner if you want the first name for each AID every day, then this works. If you need something else, you should explain what you need – t-clausen.dk Jul 17 '13 at 21:53
I need exactly what you said, first name for AID. But I am getting the second one for some reason. – Learner Jul 17 '13 at 22:01
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/33666/discussion-between-t-clausen-dk-and-learner) – t-clausen.dk Jul 17 '13 at 22:03

How to get first available value in a column (not MIN) using aggregate function on a NVARCHAR type column?

1 Answers1