Can you execute my query on your created table to check if it works?

and this one?

this is right query

select dep,MAX(salary) as max_salary FROM test GROUP BY dep

5 min, i will generate example

I mean this one?

was that original table?

this just example

1,20
2,30

ok want to clarify whether this is the output your query generated?

GROUP will NOT sum

I’m not summing salaries. I want maximum salary within each department.

ok. an online platform would have worked great. Anyway thanx

i use Dbeaver it has visual interface

@alexwid BTW, is there any easier platfom to create table. I mean those INSERT and CREATE statements really do take some time when creating a table larger no. of columns?

Select salary from test group by dep having max(salary);
SQL Error [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.salary’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

this one.

can you execute my query on your created table to check if it works?

sorry can’t answer about HAVING

Select salary from table_name group by dept having salary = max(salary); this?

maybe if group by dept but it doent work in my configuration
(this is incompatible with sql_mode=only_full_group_by)

Select emp_id, E_name, department, max(salary) from tbl group by department having max(salary)

This would not work because having clause expects aggregate function or so not just column

Like count, avg or so

Ok. So, we shouldn’t use aggregate functions inside where but should write inside the having clause. Right?

Exactly

But having will behave like where without group by

ok

Another question

t1 – id(pk),name, age

t2 – id(fk), marks

marks of a person whose age is maximum

select t2.marks

from t1 inner join t2 on t1.id = t2.id

where t1.age = (select max(age) from t1);

t1 is table1 and t2 is table2 and pk is primary key and fk – foreign key

Cpt. Jack Sparrow Is that correct?

Didn’t you run this query?

If you , you would have know!

I see a slight problem here, that this will give me the marks of the student whose age is maximum in the t1 table and not the inner join of t1 and t2 tables.

Not inner join? but you are using inner so engine will parse it as inner join

Yeah, I ran the query and it is giving right result but not in the sense that I want it to.

create table t1(id integer primary key, name varchar(40), age integer);
create table t2(id integer references t1(id), marks integer);
insert into t1 values(1, “a”, 15);
insert into t1 values(2, “b”, 16);
insert into t1 values(3, “c”, 17);
insert into t1 values(4, “d”, 18);

insert into t2 values(1, 54);
insert into t2 values(2, 54);
insert into t2 values(3, 23);
— insert into t2 values(4, 23); this is commented
insert into t2 values(5, 54);

— select * from t1;
— select * from t2;

select t2.marks from t1 inner join t2 on t1.id = t2.id where t1.age = (select max(age) from t1);

The problem is that max(age) from t1 returns 18(id is 4) and the inner join of t1 and t2 does not contain the row with id=4 and that’s why it isn’t giving me any output.

What I want is that after they are joined, then find the age marks of a student with maximum age.

you should not learn SQL in a chat! read books!

But, above one is a doubt

quite correct, yes

Use left join

You will get the desired output

Not inner

in where clause instead of selecting max(age) from t1 select max(age) from join of t1 and t2

I’ll get NULL because now id=4 is not present in t2 and the maximum age from t1 is of the person with id=4.

yes. that should work

with ON t1.id = t2.id. Isn’t it?