Query for Latest Row Determined by Two Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query for Latest Row Determined by Two Columns

Author

Topic

objectuser
Starting Member

14 Posts

Posted - 2004-10-15 : 11:20:02

Hi, everyone.

I have a query that I am so far unable to perform. I need to find the latest row in a child table that corresponds to rows in the parent. The trick is that the latest row is determined by two columns in the row, not just one. Further, I would really like to do this in a single query.

If it was one column, I can do it, and will show code below to try to clarify what I am looking for--but two columns is giving me trouble.

For example, consider the following tables:


create table p (
	id int identity primary key,
	name varchar(50)
)

create table c (
	id int identity primary key,
	p int references p(id),
	x_dt datetime,
	y_dt datetime
)

If I want to find the latest p for a particular name, determined by a single date in the child c, I can do the following:


select p.*, c.*
from p
join c on c.p = p.id
join (
	select max(x_dt) x_dt
	from c
	join p on p.id = c.p
	group by p.name) c2 on c2.x_dt = c.x_dt

However, you'll notice that c has two dates, x_dt and y_dt. What I really need is the latest c determined by the greater of x_dt and y_dt for the row.

If you'd like to play with this, here is the full schema script:


drop table c
drop table p

create table p (
	id int identity primary key,
	name varchar(50)
)

create table c (
	id int identity primary key,
	p int references p(id),
	x_dt datetime,
	y_dt datetime
)

insert into p
(name)
values ('One')

insert into p
(name)
values ('Two')

insert into p
(name)
values ('One')

insert into p
(name)
values ('One')

insert into p
(name)
values ('Two')

insert into c
(p, x_dt, y_dt)
values (1, current_timestamp - 10, current_timestamp - 1)

insert into c
(p, x_dt, y_dt)
values (2, current_timestamp - 8, current_timestamp - 5)

insert into c
(p, x_dt, y_dt)
values (3, current_timestamp - 6, current_timestamp - 2)

insert into c
(p, x_dt, y_dt)
values (4, current_timestamp - 7, current_timestamp - 4)

insert into c
(p, x_dt, y_dt)
values (5, current_timestamp - 9, current_timestamp - 3)

If I've done my example correctly, I believe this should return row 1 for name 'One', and row 5 for name 'Two' from p.

Thanks for any help!

--Kevin

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-15 : 11:27:40

[code]
Select
*
From p
Inner Join c
On p.id = c.p
Where
c.x_dt = (Select max(dt) From (Select p, dt = x_dt From c Union All Select p, dt = y_dt From c) A)
or
c.y_dt = (Select max(dt) From (Select p, dt = x_dt From c Union All Select p, dt = y_dt From c) A)
[/code]

Corey

objectuser
Starting Member

14 Posts

Posted - 2004-10-15 : 11:52:26

Thanks, Corey!

So far, I think your query will only return the latest for all of the child rows. For example, I only get one row back corresponding to a row in p with name 'One'. This is because the latest of all of the rows in c corresponds to a row in p with name 'One'. However, I don't think this would allow you to find the latest p with name 'Two'.

--Kevin

objectuser
Starting Member

14 Posts

Posted - 2004-10-15 : 12:49:54

Okay, it seems to work if I inject the name throughout all of the selects. We actually may be able to do that. I still don't know how we would make the select return the latest for each name in p, but I'm working on it.

Here's Corey's query updated with the name injected:


Select 
	p.*, c.* 
From p
Inner Join c 
On p.id = c.p
Where
p.name = 'Two'
and (
c.x_dt = (
	Select max(dt)
	From (
		Select p, dt = x_dt
		From c
		join p on p.id = c.p
		where p.name = 'Two'
		Union All
		Select p, dt = y_dt
		From c
		join p on p.id = c.p
		where p.name = 'Two') A
	)
or 
c.y_dt = (
	Select max(dt)
	From (
		Select p, dt = x_dt
		From c
		join p on p.id = c.p
		where p.name = 'Two'
		Union All
		Select p, dt = y_dt
		From c
		join p on p.id = c.p
		where p.name = 'Two') A
	)
)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-15 : 13:56:15

I misuderstood your question... try this:


Select A.*, B.*
From p A
Inner Join c B
On A.id = B.p
Inner Join
	(
	Select name, dt=max(case when x_dt>y_dt then x_dt else y_dt end)
	From p
	Inner Join c
	On p.id = c.p
	Group by name
	) C
On A.name = C.name
and (B.x_dt = C.dt or B.y_dt = C.dt)

Corey

objectuser
Starting Member

14 Posts

Posted - 2004-10-15 : 14:13:11

Wow. That works great.

Thanks, Corey!

--Kevin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources