Display a field not part of the group by?

[Phailak]

Hail,

I have 4 fields: tName, tDate, tTime, TotalUtilizationAvg (this field in in %)

What I need is to get the max amount from the Utilization field per day per Name which I do like so:

SELECT tName, tDate, max(TotalUtilizationAvg) as MaxUtil
FROM Traffic
GROUP BY BridgeName, TrafficDate

This works great, now I need to know the time at which this MaxUtil was hit (it's in increments of 15 minutes and if it was reached multiple times just need the first one) and if I add the time into the query with the group by, it'll just give me the MaxUtil field for every time of the day...

I'm assuming there's a way to display the time field in this situation but can't figure it out

 

[gmmastros]

You make your query in to a subquery and join it back tot he original table. Here's how.

Select Traffic.*
From   Traffic
       Inner Join 
          (
          SELECT tName, 
                 tDate,  
                 max(TotalUtilizationAvg) as MaxUtil
          FROM Traffic 
          GROUP BY BridgeName, TrafficDate
          ) As A 
          On Traffic.tName = A.tName
          And Traffic.tDate = A.tDate
          And Traffic.TotalUtilizationAvg = A.MaxUtil

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

This query is incorrect

SELECT tName, tDate, max(TotalUtilizationAvg) as MaxUtil
FROM Traffic
GROUP BY BridgeName, TrafficDate

you group by BridgeName and TrafficDate but you are selecting tName, tDate doesn't this throw an error?


George's solution will work for you after you fix the select/group by

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Phailak]

Thanks guys, subquery it is