Max and Min in SQL - 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 in SQL

Author

Topic

Gudiya
Starting Member

14 Posts

Posted - 2012-09-12 : 21:50:19

Hi,

I have this conditions where
-- should be getting the maximum of the date
--and take the min from the (maximum of date).

suppose, i have following different dates:
columnA ColumnB
20120520 20120620
,20120530 20120620
,20120530 20120515

so here, I need to get the minimum of column B that falls under maximum of column A.My result should be:
20120530 - 20120515.

I think that i will get max(A) group by date gives me 20120520 and 20120530.
Now frm this two dates, i need to get the minimum date that should result me in 20120515.

So can you please help me writing the query for this.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-09-12 : 22:11:35

[code]
declare @a table(col1 datetime, col2 datetime)
insert into @a select
'20120520', '20120620' union all select
'20120520', '20120513' union all select
'20120530', '20120620' union all select
'20120530', '20120515' union all select
'20120630', '20120515'

SELECT *
FROM (
SELECT *, RN = ROW_NUMBER() OVER (ORDER BY col1 DESC, col2)
FROM @a
)a
WHERE RN = 1
[/code]

Gudiya
Starting Member

14 Posts

Posted - 2012-09-12 : 22:41:02

How can I implement this because I need the max from the min of columnB.
I think i need to use sub-query but don't know how.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 23:09:12

quote:
Originally posted by Gudiya

How can I implement this because I need the max from the min of columnB.
I think i need to use sub-query but don't know how.

did you try given suggestion?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gudiya
Starting Member

14 Posts

Posted - 2012-09-12 : 23:12:08

Yes I did but could not get any result..and on the top of that I didnot understood what the query is trying to do.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 23:21:25

show your used query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-12 : 23:58:29

An alternative solution. Maybe this will be easier for you

select	columnA, ColumnB = min(ColumnB)
from	yourtable t
where	t.columnA = (select max(columnA) from yourtable)
group by columnA

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:18:59

quote:
Originally posted by Gudiya

How can I implement this because I need the max from the min of columnB.
I think i need to use sub-query but don't know how.

Just ignore the INSERT statement part and replace column name and table name

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 02:20:02

One more way

select top 1 * from @a order by col1 desc, col2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources