maxdate issue - 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

Other SQL Server Topics (2005)

maxdate issue

Author

Topic

tirby
Starting Member

3 Posts

Posted - 2009-04-13 : 14:10:21

What happens when you give a geologist a computer? You get a geologist asking stupid questions.
I’m off shore right now so I can’t run and get a book, so bare with me.
I am working with sql server 2005 on a remote connection. I can connect to and pull data. So I have a good connection and no security problems.
I have a very simple table consisting of 3 fields
Field 1 well_name
Field 2 date
Field3 water_produced

Example table

Well1 01/01/2009 25
Well1 02/01/2009 35
Well2 01/01/2009 20
Well2 02/01/2009 21

I need to create a view that shows the latest record per well name

Example
Well1 02/01/2009 35
Well2 02/01/2009 21

That is the last record based on the date
I am using Microsoft SQL Server Management Studio Express to make the view

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-13 : 14:14:53

Try this query to create the view.

select a.well_name,a.date,a.water_produced
from (select well_name,date,water_produced,row_number() over(partition by well_name order by date desc) as seq from <urtable>
) a
where a.seq = 1

tirby
Starting Member

3 Posts

Posted - 2009-04-13 : 16:22:44

Worked great didnt know i could nest the statements like that thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-13 : 16:30:11

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources