How max() function works in SQL-Server?

Question

I want to find maximum column value, i says:

SELECT 
Segment_ID.Segment_ID,
Intensity.Date,
Intensity.NumAll,
Intensity.AverageDailyIntensCar,
MAX(Intensity.AverageDailyIntensCar) as maxvalue,
Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
where (DATEPART(yy, Intensity.Date) = 2009
AND DATEPART(mm, Intensity.Date) = 08
AND DATEPART(dd, Intensity.Date) = 14)

But get error:

Column `Segment_ID.Segment_ID` is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So i add GROUP BY Segment_ID.Segment_ID but get same error for next column.
How to use max() function correctly?

UPD

I think i asking wrong. Bucouse i expect that max() function return me row and set in column MAX(Intensity.AverageDailyIntensCar) as maxvalue a max value of Intensity.AverageDailyIntensCar column. Thats right?

Is `MAX(Intensity.AverageDailyIntensCar) OVER () as maxvalue` what you need? — Martin Smith, May 22 '13 at 06:05

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

Max is an aggregate function, you can not use it with column name. if you are using Max then use group by.

Reference

edited May 23 '17 at 10:25

Community

1
1

answered May 22 '13 at 05:50

Amit

15,217
8
46
68

score 2 · Answer 2 · answered May 22 '13 at 06:29

Try this:

SELECT 
Segment_ID.Segment_ID,
Intensity.Date,
Intensity.NumAll,
Intensity.AverageDailyIntensCar,
MAX(Intensity.AverageDailyIntensCar)OVER() as maxvalue,
Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
where (DATEPART(yy, Intensity.Date) = 2009
AND DATEPART(mm, Intensity.Date) = 08
AND DATEPART(dd, Intensity.Date) = 14)

Devart · Accepted Answer · 2013-05-22T05:58:50.847

1

Try this one -

SELECT 
    s.Segment_ID,
    i.Date,
    i.NumAll,
    MAX(i.AverageDailyIntensCar) AS maxAverageDailyIntensCar,
    t.the_geom
FROM dbo.Segment_ID s
LEFT JOIN dbo.Track t ON s.Segment_ID = t.Segment_ID
LEFT JOIN dbo.Intensity i ON s.Segment_ID = i.Segment_ID
WHERE i.Date = '20090814'
GROUP BY 
    s.Segment_ID,
    i.Date,
    i.NumAll,
    t.the_geom

Update:

SELECT 
      s.Segment_ID
    , i.[Date]
    , i.NumAll
    , mx.maxAverageDailyIntensCar
    , t.the_geom
FROM dbo.Segment_ID s
LEFT JOIN dbo.Track t ON s.Segment_ID = t.Segment_ID
LEFT JOIN dbo.Intensity i ON s.Segment_ID = i.Segment_ID
LEFT JOIN (
    SELECT 
          i.Segment_ID
        , maxAverageDailyIntensCar = MAX(i.AverageDailyIntensCar)
    FROM dbo.Intensity i
    GROUP BY i.Segment_ID
) mx ON s.Segment_ID = mx.Segment_ID
WHERE i.[Date] = '20090814'

edited May 22 '13 at 05:58

answered May 22 '13 at 05:47

Devart

119,203
23
166
186

1

???Intensity.AverageDailyIntensCar should not be in group, and be used only once ... – bummi May 22 '13 at 05:48
Im update question please look at it. And in your query: Type "geometry " cant be used in group by. Its for column `the_geom` – Kliver Max May 22 '13 at 05:53
Its nice, but give uncorrect result. Its try to found max value of whole `i.maxAverageDailyIntensCar`. But not found it. – Kliver Max May 22 '13 at 06:21
For example max value in this column ~30 000 but its found only ~6000. – Kliver Max May 22 '13 at 06:26

score 0 · Answer 4 · answered May 22 '13 at 06:02

;With cte AS
(
SELECT 
Segment_ID.Segment_ID,
Intensity.Date,
Intensity.NumAll,
Intensity.AverageDailyIntensCar
Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
where (DATEPART(yy, Intensity.Date) = 2009
AND DATEPART(mm, Intensity.Date) = 08
AND DATEPART(dd, Intensity.Date) = 14)
)
Select *,(Select MAX(AverageDailyIntensCar) from CTE) as maxvalue
from CTE

Its nice and work. But i cant use `with` construction. Its possible to do it without `with`. — Kliver Max, May 22 '13 at 06:25

How max() function works in SQL-Server?

4 Answers4