Get Max value and another column from same row?

Question

How can I return max value and the date from the following table?

declare @t1 table 
(
    id int,
    value int,
    date date
)

insert into @t1
select 1, 100, '2017-01-02'
union
select 1, 200, '2017-01-03'
union
select 1, 300, '2017-01-04'
union
select 2, 400, '2017-02-02'
union
select 2, 500, '2017-02-03'
union
select 2, 600, '2017-02-04'

select id, max(value) from @t1 group by id

The following returns max value, but I also need the date from that max value. In this case, it would be 300, '2017-01-04' and 600, '2017-02-04'

Thanks.

Are you looking for the max Value on each date, or in each group? Or just the single Max Value and the associated date? — Aidan
– Aidan, Commented Jan 4, 2018 at 16:57

sthames42 · Accepted Answer · 2018-01-04 17:30:27Z

1

It looks like you want to find all data from the row with the MAX(value) for each ID. Use partitioning:

SELECT id, value, date
  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS i, *
          FROM @t1
       ) parts
 WHERE i = 1

Of course, you're going to have a problem with all of these answers if you have duplicate MAX(value)s for an id. But you can address that here by changing the partition ordering.

For instance, to find the most recent row with the MAX(value), simply add date DESC to the PARTITION clause:

SELECT id, value, date
  FROM (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC, date DESC) AS i, *
          FROM @t1
       ) parts
 WHERE i = 1

edited Jan 4, 2018 at 17:30

answered Jan 4, 2018 at 17:20

sthames42

1,03810 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Racil Hilan · Accepted Answer · 2018-01-04 16:50:32Z

0

Join your table to the query that you have by id and select whatever fields you want:

SELECT a.*
FROM t1 a
INNER JOIN (SELECT  Name, MAX(value) value
            FROM t1
            GROUP BY id) b ON a.id = b.id

answered Jan 4, 2018 at 16:50

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

Comments

Aidan · Accepted Answer · 2018-01-04 17:00:26Z

0

Try this, to get the maximum value in your table and it's associated date:

SELECT value
    ,Date
FROM @t1
where value in (
    select max(value)
    from @T1 
)

answered Jan 4, 2018 at 17:00

Aidan

1713 silver badges12 bronze badges

Comments

Mitchell · Accepted Answer · 2018-01-04 17:11:50Z

0

You can use a Common Table Expression (CTE) to get your max value per ID then join to your original table to get the data fields you want.

;WITH grp AS (SELECT id, MAX(value) max_val FROM @t1 GROUP BY id) 
SELECT t.* FROM @t1 t JOIN grp ON t.id = grp.id AND t.value = grp.max_val

The leading semicolon is optional, but best practice since CTEs require any previous SQL statements to be terminated.

edited Jan 4, 2018 at 17:11

answered Jan 4, 2018 at 16:56

Mitchell

1501 silver badge7 bronze badges

Collectives™ on Stack Overflow

Get Max value and another column from same row?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related