Link to home
Create AccountLog in
Avatar of jameslimin
jameslimin

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?
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

SELECT t1.*
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
another way to do it:


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)

...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)
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

Open in new window

When I said ascending, I meant in accordance with date....
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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).
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.
Avatar of jameslimin
jameslimin

ASKER

the ID is not unique.
i tried sarath's and using row_number()

Thank you all.
jameslimin,

Did you try my suggestion?

Regards,

Patrick