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?

What does "it's not working" mean? Also, it's 2020, time to learn the explicit 1992 ANSI JOIN syntax. Bad habits to kick : using old-style JOINs — Thom A
– Thom A ♦, Commented May 21, 2020 at 9:13
Plus you do not need any join for what you are trying to achieve. The sub-select will already return the value you are after. Just remove table2from your outer query. — Filburt
– Filburt, Commented May 21, 2020 at 9:16

Grzegorz Gierlik · Accepted Answer · 2020-05-21 09:23:12Z

3

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.

answered May 21, 2020 at 9:23

Grzegorz Gierlik

11.3k4 gold badges49 silver badges57 bronze badges

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

Comments

dnoeth · Accepted Answer · 2020-05-24 21:12:50Z

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

edited May 24, 2020 at 21:12

answered May 21, 2020 at 9:52

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Comments

marc_s · Accepted Answer · 2020-05-23 17:19:47Z

1

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

edited May 23, 2020 at 17:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 21, 2020 at 9:25

Mukesh Arora

1,8333 gold badges10 silver badges24 bronze badges

Comments

Thom A · Accepted Answer · 2020-05-21 09:22:23Z

0

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

edited May 21, 2020 at 9:22

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered May 21, 2020 at 9:21

Pato Navarro

3403 silver badges11 bronze badges

Comments

Dale K · Accepted Answer · 2020-05-21 09:50:47Z

-1

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.

edited May 21, 2020 at 9:50

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 21, 2020 at 9:38

Pramuda Liyanage

12 bronze badges

Comments

Dale K · Accepted Answer · 2020-05-21 09:51:08Z

-1

Try this:

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

edited May 21, 2020 at 9:51

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 21, 2020 at 9:27

prince kumar

1

Collectives™ on Stack Overflow

Select max value (sql)

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related