Using Derived Table
-
Dear all,
I heard that using derived table is quite faster than using correlate subquery.
I tried to turn this query into derived table but it shows different result. Would someone please help me ?
select *
from z_inquiry o
where inquirydatetime = (
select max(inquirydatetime) as maxinq_dt
from z_inquiry i
where o.frontend_id = i.frontend_id
group by frontend_id
)and frontend_id = (
select min(frontend_id) as minfrontend_id
from z_inquiry i
where o.inquirydatetime = i.inquirydatetime
group by inquirydatetime
)
Reference: http://www.databasejournal.com/features/mssql/article.php/1438861
Thank you for your help.
Best regards,
CJira
-
What derived table did you try? What are you trying to achieve?
select o.*
from z_inquiry o
inner join
where inquirydatetime = (
select frontend_id, max(inquirydatetime) as maxinq_dt
from z_inquiry i
group by frontend_id
) as i
on o.frontend_id = i.frontend_id
and o.inquirydatetime = i.maxinq_dt
inner join
(
select inquirydatetime, min(frontend_id) as minfrontend_id
from z_inquiry i
group by inquirydatetime
) d
on o.frontend_id = minfrontend_id
and o.inquirydatetime = d.inquirydatetime
Russel Loski, MCSE Business Intelligence, Data Platform
-
Must be something like this:
select o.*
from z_inquiry o
INNER JOIN (
select max(inquirydatetime) as maxinq_dt, frontend_id
from z_inquiry
group by frontend_id ) i ON o.frontend_id = i.frontend_id
and o.inquirydatetime = i.maxinq_dt
_____________
Code for TallyGenerator -
Hello Russel and Sergiy,
Thank you for your suggestion but both queries got a diffrent result from mine.
Let me change the objective a little bit
To visualize, this is input:
PATIENT ARM BESTRES DELAY 01 A CR 0 02 A PD 1 03 B PR 1 04 B CR 2 05 C SD 1 06 C SD 3 07 C PD 2 01 A CR 0 03 B PD 1 The output I would like is
PATIENT ARM BESTRES DELAY 01 A CR 0 02 A PD 1 04 B CR 2 03 B PD 1 03 B PR 1 07 C PD 2 05 C SD 1 The second PATIENT = 1 is eliminated because it has the same ARM and BESTRES as the first PATIENT = 1. The PATIENT =6 is eliminated because it has ARM and BESTRES as PATIENT =5. We would like to keep only the record that contain the first occurrence of ARM and BESTRES in each value.
To create a table
create table t (
patient char(2),
arm char(2),
bestres char(2),
delay tinyint
)
insert into t values ('01', 'A', 'CR', 0);
insert into t values ('02', 'A', 'PD', 1);
insert into t values ('03', 'B', 'PR', 1);
insert into t values ('04', 'B', 'CR', 2);
insert into t values ('05', 'C', 'SD', 1);
insert into t values ('06', 'C', 'SD', 3);
insert into t values ('07', 'C', 'PD', 2);
insert into t values ('01', 'A', 'CR', 0);
insert into t values ('03', 'B', 'PD', 1);
Best regards,
Chaivat
-
Your example has a flaw. What is the difference between the two Patient 01 rows? There is nothing to distinguish them. You need some key: a date, an identity.
Russel Loski, MCSE Business Intelligence, Data Platform
-
This would return the result you ask for if I had a way to distinguish the two patient 01 rows
select t.Patient, t.arm, t.bestres, t.delay
from t
inner join
(
select
arm, bestres, min(patient) as patient
from t
group by arm, bestres
) s
on s.arm = t.arm
and s.bestres = t.bestres
and s.patient = t.patient
Russel Loski, MCSE Business Intelligence, Data Platform
-
Dear all,
RLoski response work similar to my method. In order to distinguish two 01, I put the original query as a temp table x and select distinct * of it.
select distinct * from (
select t.Patient, t.arm, t.bestres, t.delay
from t
inner join
(
select
arm, bestres, min(patient) as patient
from t
group by arm, bestres
) s
on s.arm = t.arm
and s.bestres = t.bestres
and s.patient = t.patient
) x
C Jira
-
But, this is not my objective as said in Message 4.
We would like to keep only the record that contain the first occurrence of ARM and BESTRES in each value.
After thinking for 2-3 days, I got this code.
select distinct * from
(
select *
from t o
where patient =
(select top 1 patient
from t i
where o.arm = i.arm and
o.bestres = i.bestres
) and delay =
(select top 1 delay
from t i
where o.arm = i.arm and
o.bestres = i.bestres)
) x
Patient and delay is locked for each occurence of ARM and BESTRES.
If there is any duplicate, just select distinct * of it.
Thank you for your comments.
Best regards,
C Jira
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply