MAX function in t-sql query

Question

I am trying to get max column value across a few tables. For instance in the below snippet (it does not work) i am trying to get MAX value from a column name 'tickvalue' which is in all 3 tables A, B and C. Not sure how to get the value.

Data type of tickvalue column is timestamp.

select 
c.somkey
,MAX(a.tickvalue, b.tickvalue, c.tickvalue) as tickvalue
from A as a
join B as b
on a.key = b.key
join C as c
on b.somekey = c.somkey
where a.key = '<some key>'

Thanks

What do you expect to get in the first column of the resulting recordset? But it looks like you already know that it will be an empty string. — Igor
– Igor, Commented Nov 28, 2012 at 0:31
@Igor I just wanted to convey my intent through the snippet. The snippet does not work. — user1599610
– user1599610, Commented Nov 28, 2012 at 1:25

Beno · Accepted Answer · 2012-11-28 01:46:15Z

That is not how the MAX() aggregate function works in TSQL (see here: TSQL MAX()) The MAX function gets the maximum value in a single column only.

To achieve what you want here, you'll have to get the three values into a single column. This can be done using UNPIVOT PIVOT/UNPIVOT

This snippet might help you out.

CREATE TABLE #A (ID INT, tickvalue INT)
CREATE TABLE #B (ID INT, tickvalue INT)
CREATE TABLE #C (ID INT, tickvalue INT)

INSERT INTO #A VALUES (1,1)
INSERT INTO #A VALUES (2,4)
INSERT INTO #A VALUES (3,7)
INSERT INTO #A VALUES (4,2)
INSERT INTO #A VALUES (5,8)

INSERT INTO #B VALUES (1,8)
INSERT INTO #B VALUES (2,7)
INSERT INTO #B VALUES (3,2)
INSERT INTO #B VALUES (4,4)
INSERT INTO #B VALUES (5,1)

INSERT INTO #C VALUES (1,2)
INSERT INTO #C VALUES (2,1)
INSERT INTO #C VALUES (3,2)
INSERT INTO #C VALUES (4,15)
INSERT INTO #C VALUES (5,2)


SELECT ID, MAX(tickvalue)
FROM
(SELECT C.ID, A.tickvalue t1, B.tickvalue t2, C.tickvalue t3
FROM #A AS a
JOIN #B AS b ON a.ID = b.ID
JOIN #C AS c ON b.ID = c.ID) p
UNPIVOT (tickvalue FOR tsource IN (t1,t2,t3)) AS unpvt
GROUP BY ID

DROP TABLE #A
DROP TABLE #B
DROP TABLE #C

Niladri Biswas · Accepted Answer · 2012-11-28 03:02:43Z

1

Oracle has got Greatest function that does the same thing. Some times back I wrote an equivalent of the same in Sql Server which is available here. Have a look and I think that will serve the purpose.

answered Nov 28, 2012 at 3:02

Niladri Biswas

4,1712 gold badges19 silver badges24 bronze badges

Comments

Oleksandr Fedorenko · Accepted Answer · 2012-11-28 09:01:09Z

1

Alternative of use UNPIVOTE

SELECT c.ID,
       CASE WHEN a.tickvalue >= b.tickvalue THEN
                                                 CASE WHEN a.tickvalue >= c.tickvalue THEN a.tickvalue ELSE c.tickvalue END
                                            ELSE CASE WHEN b.tickvalue >= c.tickvalue THEN b.tickvalue ELSE c.tickvalue END END
FROM A a JOIN B b ON a.ID = b.ID
         JOIN C c ON b.ID = c.ID

answered Nov 28, 2012 at 9:01

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Collectives™ on Stack Overflow

MAX function in t-sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related