CODEWITHSUNDEEP
sql-server-2008t-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Get Max value and another column from same row?

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.

Upvotes: 1

Views: 49

Answers (4)

sthames42
sthames42

Reputation: 1009

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

Upvotes: 1

Mitchell
Mitchell

Reputation: 150

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.

Upvotes: 0

Aidan
Aidan

Reputation: 171

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 
)

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25341

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

Upvotes: 0

Related Questions