Help on specific Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help on specific Query

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2008-04-12 : 08:11:09

Hello Valueable Friends , I have an issue and i am going to need a help..

I have a rents table with fields that matter now
Stuffid , rentdate , rentCustomer ........

On my stuff table i Added a new field called LastCustRent

So I want to update this field With the Customerid that rented each
stuff last time

I tried this




Select Stuffid  ,max(rentdate) as maxrentdate , rentcust
from rents 
Group by rentcust , stuffid

But I do not take unique results

Can someone help me on how will i get only the customer of my last rent time for each stuffid on my rents table?

I sell my mother in law.Is anybody interested?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-12 : 10:31:01

Hi,
please post u r sample Data

cmspot
Starting Member

44 Posts

Posted - 2008-04-12 : 12:28:46

quote:
Originally posted by ranganath

Hi,
please post u r sample Data

Dear Friend ,
Allow me to disaggree with your request by telling you That i beleive that you do not need any other data from my project.Maybe you need better and cleaner analysis of my request.

well Imagine a Movies Table with more than 10000 rows.

Also imagine a rents table with important fields for now:

rentdate (the date of rental)

Custrent (The customer id Who rented the movie)

Movieid (The id of the movie that was rented)

I added a new field on my movies table (LastCustrent) Which keeps
the last customer id who made the rental.It is beeing filled automatically after the insertion of a rental.

Now In my Movies table this field is null in all rows and I am looking for a way to Update this field from My rents table by finding from the rental date the last customer who made the rent.

Maybe Now My request is more easy to understand...
Thank you for your valuable time....

I sell my mother in law.Is anybody interested?

cmspot
Starting Member

44 Posts

Posted - 2008-04-12 : 12:36:22

I wish I made it Cleaner!I must Finish it

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-12 : 13:49:46

seems like this is what you're looking for:-

UPDATE m
SET m.LastCustrent=t.Custrent
FROM movies m
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY Movieid ORDER BY rentdate  DESC) AS RowNo,
rentdate,
Custrent,
Movieid
FROM rents
)t
ON t.Movieid=m.Movieid
AND t.RowNo=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources