Select max value (sql)

Question

I have two tables

id|columnA|columnB|columnC
x |   a   |   b   |  c

and the second table:

 id   |id table1|columnD
    z |   x     |   3   
    y |   x     |  10  

I'm trying to get the max value from the second table and put it in the first table like this:

 id|columnA|columnB|columnC|columnD
 x |   a   |   b   |  c    |10

This is what I tried but it's not working

select id, columnA, columnB, columnC
  , (select max(columnD) from table2 where table1.id = table2.idtable1),
from table1, table2
where table1.id = table2.idtable1 

Any help?

Answer 1

You join table2 twice and this seems to be unnecessary.

I would write this like that:

select t1.id, t1.columnA, t1.columnB, t1.columnC, max(t2.columnD)
from table1 t1
join table2 t2 -- join table2 by id
  on t2.id = t1.id
group by t1.id, t1.columnA, t1.columnB, t1.columnC
order by t1.id;

This avoid subquery which can affect performance.

Answer 2

Another solution doing aggregation first and then joining:

select id, columnA, columnB, columnC, maxD
from table1
left join -- outer join in case there's no matching row in table2
 (
   select idtable1, max(columnD) as maxD
   from table2
   group by idtable1
 ) as t2
on table1.id = t2.idtable1 

Answer 3

Try below query to get the desired result - modified query you have tried.

Sample data and table:

declare @table1 table
(id varchar(10), columnA varchar(10), columnB varchar(10), columnC varchar(10))

insert into @table1
values
('x' ,   'a'  ,   'b'   ,  'c')

declare @table2 table
(id varchar(10), idtable1 varchar(10), columnD int)

insert into @table2
values
('z', 'x', 3),
('y', 'x', 10)

Query:

select 
    t1.id, columnA, columnB, columnC,
    (select max(columnD) from @table2 t2 where t1.id = t2.idtable1) columdMax
from 
    @table1 t1

Answer 4

select t2.idtable1, t1.columnA, t1.columnB, t1.columnC, max(t2.columnD)
from table2 t2
left join table1 t1 
on t1.id = t2.idtable1
group by t2.idtable1, t1.columnA, t1.columnB, t1.columnC

Answer 5

Get the maximum value of a numeric column use the MAX() function.

SELECT MAX(<numeric column>) FROM <table>;

SELECT MAX(<numeric column>) FROM <table> GROUP BY <other column>;

To get the minimum value of a numeric column use the MIN() function.

Answer 6

Try this:

select Top 1 table1.id, columnA, columnB, columnC, columnD
from table1, table2
where table1.id = table2.idtable1
order by columnD desc