JOIN, WHERE and calculated column (based on JOIN and WHERE)
Looking for the help. I try to run a code in this order JOIN, WHERE and and GRP number (on the results of my query).
I have two tables test and text:
create table test (
code varchar(10) primary key,
num int(10) not null,
name varchar(10) not null,
surname varchar(10) not null);
insert into test values (1,09,'Tom', 'Smith');
insert into test values (2,09,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');
insert into test values (8,15,'Adam','XYZ');
insert into test values (9,43,'John','Abc');
insert into test values (10,99,'Adam','Abc');
insert into test values (11,99,'John','Abc');
insert into test values (12,1,'A','A');
insert into test values (13,1,'J','A');
and table text:
create table text (
code varchar(10) primary key,
codeFK varchar(10) not null,
state varchar(10));
insert into text values (1,2,'O');
insert into text values (2,2,'O');
insert into text values (3,2,'O');
insert into text values (4,2,'C');
insert into text values (5,3,'O');
insert into text values (6,3,'O');
insert into text values (7,3,'O');
insert into text values (8,2,'O');
insert into text values (9,2,'C');
insert into text values (10,2,'O');
insert into text values (11,2,'C');
insert into text values (12,1,'C');
insert into text values (13,12,'C');
insert into text values (14,13,'C');
I join them and try to add coulm group as per below code:
JOIN - it works
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
ORDER BY test.num DESC;
GRP calculation on its on
select t.*,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp
from test t ORDER BY GRP;
But my question is how to get GRP column with above join query? So, the grp column will be calculated only on the results based on WHERE statement and not the whole table test.
I tried below but it gives me an error:
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
AND test.num IN (select t.num,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp from test t)
ORDER BY test.num DESC;