Selecting multiple records based on date

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

General SQL Server Forums

New to SQL Server Programming

Selecting multiple records based on date

Author

Topic

ttocs
Starting Member

3 Posts

Posted - 2008-07-09 : 09:27:14

Hello.

I am trying to write a query and running into some trouble. I am trying to to select only records that have the most recent date. Let me try to explain.

I am trying to select rows from a table that have multiple rows for each user. A record is created whenever an update is made to the user and it populates the table with a timestamp. Something like this

User Date
1 1/2
1 1/9
1 1/21
2 1/18
2 1/22
3 1/20
3 1/22
4 1/2
5 1/17
5 1/29

Of the records, I would want to select the following:

1 1/21
2 1/22
3 1/22
4 1/2
5 1/29

I have been trying to mess around with MAX(date), but that gives me the max date of the table, right? I need the max date for each user set and that particular record. Is this possible? Any help would be appreciated as I am going nuts :)

Thank you!

singularity
Posting Yak Master

153 Posts

Posted - 2008-07-09 : 09:29:06

select user, max(date)
from yourtable
group by user

ttocs
Starting Member

3 Posts

Posted - 2008-07-09 : 09:42:14

I thought that would work, but it doesn't. I have a bunch of tables linked into this. Let me try to explain it a little more.

The actual query is pulling website records that have a unique key and their parent key. I am then pulling multiple other records that describe the website. Finally, I'm pulling a payout record that has an amount and a date. Whenever a price change is entered, it puts in a new row in this table. When I use the MAX(Date), I'm getting the most recent date but if the payout types are different (partial payment, full, etc.) for each given record, then it returns multiple for the website.

I only want to pull the most recent change, period, regardless of the payment type.

Site Payment Method Amount Date
1 Full .50 1/2
1 Partial .75 1/19
1 Full .50 1/21
2 Full 1 2/1
2 Partial .80 2/19
2 Partial 3 2/24

Right now, using the max function, I get the following from the list above...

1 Partial .75 1/19
1 Full .50 1/21
2 Full 1 2/1
2 Partial 3 2/24

What I want is...

1 Full .50 1/21
2 Partial 3 2/24

This way it takes into account the date, but ignores the payout type. Instead of looking for the highest date based on the user itself, it looks like it's breaking it down further by payment type. Any way to ignore the payment type and just go by user only?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 09:49:11

are you using SQL Server 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

ttocs
Starting Member

3 Posts

Posted - 2008-07-09 : 09:54:22

I think 2005. I'm not sure. I work in Tech Support for a company and I believe we use 2005. I'm using TOAD to access the DB though.

Sorry, still in the learning process with everything SQL.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 10:03:04

for 2005, try this


select *
from   (
           select *, row_no = row_number () over (partition by Site ordre by Date desc)
           from   table
       )  t
where  row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-09 : 11:52:26

If I understand you correctly, then something like this should work for you.



SELECT YT1.*
FROM YourTable YT1 
     INNER JOIN (SELECT [Site], MAX([Date])As MaxDate FROM Yourtable GROUP BY [Site]) YT2
       ON YT1.Site = YT2.Site AND YT1.Date = YT2.MaxDate

dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources