Max and MIN 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

General SQL Server Forums

New to SQL Server Programming

Max and MIN Query

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-17 : 15:01:16

I have a field that has 3 dates in it

2007-03-26 00:00:00.000
2010-02-15 00:00:00.000
2012-10-29 00:00:00.000

I can get the max date which is easy.

Select Distinct pcst_id1, max(pcst_dat2) as MAXBoardDecisionMade
from impact.dbo.pcst group by pcst_id1

Now out of the three I need to pull the previous date which would be 2010-02-15 00:00:00.000. How can I do that?

I was thinking of something like below but it give me an error.

MAXBoardDecisionMade <> pcst_dat2 then Max(pcst_dat2)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-17 : 15:34:35

something like this:

select * from (
select pcst_id1, pcst_dat2 as MAXBoardDecisionMade ,
row_number() over(order by pcst desc partition by pcst_id1) r
from impact.dbo.pcst
)
where r=2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 00:41:57

quote:
Originally posted by LoztInSpace

something like this:

select * from (
select pcst_id1, pcst_dat2 as MAXBoardDecisionMade ,
row_number() over(order by pcst desc partition by pcst_id1) r
from impact.dbo.pcst
)
where r=2

shouldnt partition by and order by orders be swapped? ie partition by followed by order by?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-18 : 07:03:37

visakh16, yes you are correct. Now I am having an issue with the where clause. Everything else works, but it is saying that the "r" is invalid.

select pcst_id1, pcst_dat2 as MaxboardDecisionMade,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
where r= '2'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 07:07:13

you cant use alias like that in where. for that you need to create a derived table out of query and use it


select pcst_id1,MaxboardDecisionMade
from
(
select pcst_id1, pcst_dat2 as MaxboardDecisionMade,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wsilage
Yak Posting Veteran

82 Posts

Posted - 2013-07-18 : 09:44:57

Thanks everyone for you help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 09:47:20

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources