asked on
T-SQL for last date
i have table
ID Product Purc_Date Customer rate
01 Manggo 1/1/2009 John .01
02 Peach 1/3/2009 John .024
03 Apple 1/3/2009 Marry .2
04 Lemon 2/13/2009 Dave .4
05 Grape 2/15/2009 John .3
06 Peach 2/15/2009 Dave .1
I want to get last purchased date on each customer.
I want my returned querry will be like this :
ID Product Purc_Date Customer rate
03 Apple 1/3/2009 Marry .2
05 Grape 2/15/2009 John .3
06 Peach 2/15/2009 Dave .1
can anybody help me out?
ID Product Purc_Date Customer rate
01 Manggo 1/1/2009 John .01
02 Peach 1/3/2009 John .024
03 Apple 1/3/2009 Marry .2
04 Lemon 2/13/2009 Dave .4
05 Grape 2/15/2009 John .3
06 Peach 2/15/2009 Dave .1
I want to get last purchased date on each customer.
I want my returned querry will be like this :
ID Product Purc_Date Customer rate
03 Apple 1/3/2009 Marry .2
05 Grape 2/15/2009 John .3
06 Peach 2/15/2009 Dave .1
can anybody help me out?
Microsoft SQL Server
Last Comment
Patrick Matthews
another way to do it:
select * from tablename t
where id = (select max(id) from tablename t2 where t.customer = t2.customer)
select * from tablename t
where id = (select max(id) from tablename t2 where t.customer = t2.customer)
and another... not as trusting of ID's as above, other than being unique - is that correct - they are unique ?
select * from tablename t
where id = (select top 1 id from tablename t2 where t.customer = t2.customer order by date desc)
select * from tablename t
where id = (select top 1 id from tablename t2 where t.customer = t2.customer order by date desc)
...trust the IDs...they're your friend. :)
yeah yeah... Please explain my little friends now... Think they are more like "critters" you feed then a bit of SQL and they do strange things. Always unique though, just not ascending...
--drop table #tst
create table #tst (id int identity, date datetime, customer int)
insert #tst
select '20090102',1 union all
select '20090103',1 union all
select '20090202',1 union all
select '20090304',1 union all
select '20090105',1
select * from #tst t
where id = (select top 1 id from #tst t2 where t.customer = t2.customer order by date desc)
select * from #tst t
where id = (select max(id) from #tst t2 where t.customer = t2.customer)
--drop table #tst
create table #tst (id int identity, date datetime, customer int)
insert #tst
select '20090102',1 union all
select '20090103',1 union all
select '20090202',1 union all
select '20090304',1 union all
select '20090105',1
select * from #tst t
where id = (select top 1 id from #tst t2 where t.customer = t2.customer order by date desc)
select * from #tst t
where id = (select max(id) from #tst t2 where t.customer = t2.customer)
And forgot matthews...
--drop table #tst
create table #tst (id int identity, date datetime, customer int)
insert #tst
select '20090102',1 union all
select '20090103',1 union all
select '20090202',1 union all
select '20090304',1 union all
select '20090304',1 union all
select '20090105',1
select * from #tst t
where id = (select top 1 id from #tst t2 where t.customer = t2.customer order by date desc)
select * from #tst t
where id = (select max(id) from #tst t2 where t.customer = t2.customer)
SELECT t1.*
FROM #tst t1 INNER JOIN
(SELECT t2.Customer, MAX(t2.date) AS date
FROM #tst t2
GROUP BY t2.Customer) z ON t1.Customer = z.Customer AND t1.date = z.date
ORDER BY t1.ID
When I said ascending, I meant in accordance with date....
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Dense_rank, rank or row_number would work, and I think that approach won the last time there was a problem like this - should have remembered that one.
The bottom line is when searching or seeking the max date, somewhere somehow you do need to sort by date and retrieve the top 1. Unless you do want all matching entries.
As far as if more than one qualify for max date then that is up to the Asker. Just pointing it out via code, and not making any comments (other than id's do not mean date ascending).
The bottom line is when searching or seeking the max date, somewhere somehow you do need to sort by date and retrieve the top 1. Unless you do want all matching entries.
As far as if more than one qualify for max date then that is up to the Asker. Just pointing it out via code, and not making any comments (other than id's do not mean date ascending).
Yes, there is no point in considering IDs if you are dealing with Customer with last date. dont trust the IDs in this case if you don't have IDs in sequential order.
ASKER
the ID is not unique.
i tried sarath's and using row_number()
Thank you all.
i tried sarath's and using row_number()
Thank you all.
jameslimin,
Did you try my suggestion?
Regards,
Patrick
Did you try my suggestion?
Regards,
Patrick
FROM SomeTable t1 INNER JOIN
(SELECT t2.Customer, MAX(t2.Purc_Date) AS Purc_Date
FROM SomeTable t2
GROUP BY t2.Customer) z ON t1.Customer = z.Customer AND t1.Purc_Date = z.Purc_Date
ORDER BY t1.ID