May 2, 2012 at 2:40 pm
create table client(client_id int primary key ,client_name varchar(22))
create table contact (contact_id int primary key,client int foreign key (client) references client(client_id),
dates datetime,phone varchar(13))
insert into client values(1,'jt'),
(2,'nk'),
(3,'dil'),
(4,'jig'),
(5,'gori')
select * from contact
select * from client
insert into contact values(10,1,'2012-04-29' ,'123')
insert into contact values(11,1,'2012-05-01' ,'321')
insert into contact values(12,2,'2012-04-29' ,'1234')
insert into contact values(13,2,'2012-05-01' ,'4321')
insert into contact values(14,3,'2012-24-27' ,'12345')
insert into contact values(15,3,'2012-05-29' ,'54321')
insert into contact values(16,4,'2012-01-01' ,'123456')
insert into contact values(17,4,'2012-06-29' ,'654321')
now i want clinet_name, phone from tables.but only those records with new dates .for e.g in contact table
for client(1) i want records with new phone.we can see that when phone number get change dates column updated with new value.i want only row with new phone number
similary i want records for all client with new phone number.
i want result in this way
client_name------- phone
jt--------------- 321
nk---------------- 4321
dil------------------54321
jig------------------654321
May 2, 2012 at 2:52 pm
Try returning MAX(dates) from contact table, grouping by client number.
_________________________________
seth delconte
http://sqlkeys.com
May 2, 2012 at 2:53 pm
This should work for you. Thanks for the clear explanation and ddl and sample data. That makes this kind of stuff a zillion times easier.
select * from
(
select ROW_NUMBER() over (PARTITION by client_id order by dates desc) as RowNum, client_name, phone
from client c
join Contact ct on c.client_id = ct.client
) x
where x.RowNum = 1
btw, I hope this is just sample ddl to represent your actual challenge. You have a datacolumn that is changing between the tables. client and client_id, these should always have the same name so you don't have to try to figure out which name belongs to which table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 3:05 pm
This?
;with contacts as
(
select c.* , rn = ROW_NUMBER() over (partition by client order by dates desc )
from contact c
)
select clt.client_name , ctc.phone
from client clt
join contacts ctc
on ctc.client = clt.client_id
and ctc.rn =1
May 2, 2012 at 3:06 pm
Oops, Sean beat me to it, a long time ago ... 😀
May 2, 2012 at 3:18 pm
ColdCoffee (5/2/2012)
Oops, Sean beat me to it, a long time ago ... 😀
Yes but a slightly different approach to cracking the same nut. Same result, different execution plan. Hard to say with such little data but they seem to perform about the same.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2012 at 3:45 pm
Another crude method
select client_name,phone
from(
select temp.client,phone
from
(select [client],max(DATEs) datephone
from contact
group by client)temp
inner join contact
on contact.client = temp.client
and contact.dates = temp.datephone)tmp
inner join client
on client.client_id = tmp.client
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply