32 GB of Memory

Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.
In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
In SQL Server, run:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'min server memory (MB)', 30720
go
reconfigure with override
go
Stop and start SQL Server.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.
|||Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>
|||Correct. Looks like my cut and paste missed the mark. :-(
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ORxSH0QyHHA.988@.TK2MSFTNGP02.phx.gbl...
Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>
|||What makes me more confused is your mention of "I am going to be
upgrading our servers from SP3a to SP4. ", any specific reason you
mentioned it, because you can allocate 32 GB of memory even if you are
on SP3a. Also you should be aware of Hot Add memory
Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
AWE:
899761FIX: Not all memory is available when AWE is enabled on a
computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761
|||Is it possible to take advantage of the additional memory on Windows Server
2000 Enterprise edition or 2003 only?
"dinu_babu@.hotmail.com" wrote:

> What makes me more confused is your mention of "I am going to be
> upgrading our servers from SP3a to SP4. ", any specific reason you
> mentioned it, because you can allocate 32 GB of memory even if you are
> on SP3a. Also you should be aware of Hot Add memory
> Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
> AWE:
> 899761FIX: Not all memory is available when AWE is enabled on a
> computer that is running a 32-bit version of SQL Server 2000 SP4
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761
>
>

32 GB of Memory

Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
In SQL Server, run:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'min server memory (MB)', 30720
go
reconfigure with override
go
Stop and start SQL Server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.|||Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||Correct. Looks like my cut and paste missed the mark. :-(
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ORxSH0QyHHA.988@.TK2MSFTNGP02.phx.gbl...
Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||What makes me more confused is your mention of "I am going to be
upgrading our servers from SP3a to SP4. ", any specific reason you
mentioned it, because you can allocate 32 GB of memory even if you are
on SP3a. Also you should be aware of Hot Add memory
Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
AWE:
899761 FIX: Not all memory is available when AWE is enabled on a
computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761|||Is it possible to take advantage of the additional memory on Windows Server
2000 Enterprise edition or 2003 only?
"dinu_babu@.hotmail.com" wrote:
> What makes me more confused is your mention of "I am going to be
> upgrading our servers from SP3a to SP4. ", any specific reason you
> mentioned it, because you can allocate 32 GB of memory even if you are
> on SP3a. Also you should be aware of Hot Add memory
> Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
> AWE:
> 899761 FIX: Not all memory is available when AWE is enabled on a
> computer that is running a 32-bit version of SQL Server 2000 SP4
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;899761
>
>

32 GB of Memory

Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
In SQL Server, run:
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'min server memory (MB)', 30720
go
reconfigure with override
go
Stop and start SQL Server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JDS" <JDS@.discussions.microsoft.com> wrote in message
news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
Accroding to the following Article:
http://support.microsoft.com/?id=274750
Note The maximum amount of memory that can be supported on Windows Server
2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB
of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
physical RAM by using the Physical Address Extensions (PAE) feature. You can
use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
Microsoft Windows Server 2003 Datacenter Edition.
This statement is a bit confusing. I am going to be upgrading our servers
from SP3a to SP4. I am planning to implement this on windows server 2003
Enterprise Edition. Will I be able to allocate 32 GB of memory to this
server. If so what steps do I take. Also will there be any limitations.
Also if there is any other documentation regarding this. Let me know.|||Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||Correct. Looks like my cut and paste missed the mark. :-(
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ORxSH0QyHHA.988@.TK2MSFTNGP02.phx.gbl...
Tom
> sp_configure 'min server memory (MB)', 30720
Should it be max memory?
JDS
In addition to Tom's suggestion do not forget to download hotfix for SP4
http://support.microsoft.com/kb/899761
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ujghB0KyHHA.5592@.TK2MSFTNGP04.phx.gbl...
> In BOOT.INI add the /PAE switch and do not use the /3GB switch. Reboot.
> In SQL Server, run:
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'min server memory (MB)', 30720
> go
> reconfigure with override
> go
> Stop and start SQL Server.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JDS" <JDS@.discussions.microsoft.com> wrote in message
> news:9449E5A9-D31E-4C5B-86C8-86B5E3686AFC@.microsoft.com...
> Accroding to the following Article:
> http://support.microsoft.com/?id=274750
> Note The maximum amount of memory that can be supported on Windows Server
> 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32
> GB
> of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of
> physical RAM by using the Physical Address Extensions (PAE) feature. You
> can
> use the 3 GB switch that is in the Boot.ini file with Microsoft Windows
> Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with
> Microsoft Windows Server 2003 Datacenter Edition.
>
> This statement is a bit confusing. I am going to be upgrading our servers
> from SP3a to SP4. I am planning to implement this on windows server 2003
> Enterprise Edition. Will I be able to allocate 32 GB of memory to this
> server. If so what steps do I take. Also will there be any limitations.
> Also if there is any other documentation regarding this. Let me know.
>|||What makes me more confused is your mention of "I am going to be
upgrading our servers from SP3a to SP4. ", any specific reason you
mentioned it, because you can allocate 32 GB of memory even if you are
on SP3a. Also you should be aware of Hot Add memory
Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
AWE:
899761 FIX: Not all memory is available when AWE is enabled on a
computer that is running a 32-bit version of SQL Server 2000 SP4
http://support.microsoft.com/defaul...kb;EN-US;899761|||Is it possible to take advantage of the additional memory on Windows Server
2000 Enterprise edition or 2003 only?
"dinu_babu@.hotmail.com" wrote:

> What makes me more confused is your mention of "I am going to be
> upgrading our servers from SP3a to SP4. ", any specific reason you
> mentioned it, because you can allocate 32 GB of memory even if you are
> on SP3a. Also you should be aware of Hot Add memory
> Also just to add to Tom's reply, make sure you apply hotfix on SP4 for
> AWE:
> 899761 FIX: Not all memory is available when AWE is enabled on a
> computer that is running a 32-bit version of SQL Server 2000 SP4
> http://support.microsoft.com/defaul...kb;EN-US;899761
>
>

2nd Largest value

Hi,
How do i retrieve 2nd Largest Amount in a table using Query:
Table: sales
Sno. Name Amount
111 abc 10000
222 xyz 23000
333 mbn 100
444 iriri 50000
-DNK
CREATE TABLE #SampleTable
(
SomeValue INT
)
INSERT INTO Select 10
INSERT INTO #SampleTable Select 29
INSERT INTO #SampleTable Select 8
INSERT INTO #SampleTable Select 8
INSERT INTO #SampleTable Select 9
INSERT INTO #SampleTable Select 50
Select TOP 1 SomeValue from
(
Select TOP 2 SomeValue from #SampleTable Order by SomeValue
DESC
) sv
Order by SomeValue ASC
--OR
Select TOP 1 SomeValue from #SampleTable
WHERE SomeValue not in
(
Select TOP 1 SomeValue from #SampleTable Order by SomeValue
DESC
)
Order by SomeValue DESC
HTH, Jens Suessmeyer.|||Try:
select
*
from
dbo.MyTable a
where
2 = (select count (*)
from dbo.MyTable b
where b.Amount > a.Amount
or (b.Amount = a.Amount
and b.Sno > a.Sno)
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"DNKMCA" <dnk@.msn.com> wrote in message
news:e7G4WppvFHA.3236@.TK2MSFTNGP14.phx.gbl...
Hi,
How do i retrieve 2nd Largest Amount in a table using Query:
Table: sales
Sno. Name Amount
111 abc 10000
222 xyz 23000
333 mbn 100
444 iriri 50000
-DNK|||Try this:
select top 1 Amount
from (
select top 2 Amount
from dbo.sales
order by Amount desc
) topTwo
order by topTwo.Amount
ML|||Try one of the following. If there are duplicate amounts then the
second query may return more than one row or there may be more than one
row with a higher amount than that returned. This may or may not be
what you want - it all depends on how you define "2nd largest".
SELECT MAX(amount)
FROM sales
WHERE amount <
(SELECT MAX(amount)
FROM sales) ;
SELECT sno, name, amount
FROM sales
WHERE amount =
(SELECT MAX(amount)
FROM sales
WHERE amount <
(SELECT MAX(amount)
FROM sales)) ;
David Portas
SQL Server MVP
--|||DNKMCA
try this
select min(a.amount) from
(select top 2 amount from pay503)a
Regards
R.D
"DNKMCA" wrote:

> Hi,
> How do i retrieve 2nd Largest Amount in a table using Query:
> Table: sales
> Sno. Name Amount
> 111 abc 10000
> 222 xyz 23000
> 333 mbn 100
> 444 iriri 50000
> -DNK
>
>|||select min(a.amount) from
(select top 2 amount from SALES)a
"R.D" wrote:
> DNKMCA
> try this
> select min(a.amount) from
> (select top 2 amount from pay503)a
> Regards
> R.D
>
> "DNKMCA" wrote:
>|||OOPS
USE ORDERBY IN DERIVED TABLE
select min(a.amount) from
(select top 2 amount from sales order by amount desc) a
This query will have least overhead of all.
"R.D" wrote:
> DNKMCA
> try this
> select min(a.amount) from
> (select top 2 amount from pay503)a
> Regards
> R.D
>
> "DNKMCA" wrote:
>|||Notice that this will give a different result to my version if there is
a tie for the number 1 spot.
Also notice that your TOP query will give the wrong answer if there is
only one row in the Sales table. Maybe that's not a realistic scenario
in this case but it is an example of how tricky TOP is. TOP has a
number of catches. Another one is that it sorts NULLs first, so TOP 1
in ascending order may give you a different result to MIN.
David Portas
SQL Server MVP
--|||David
>if there is
only one row in the Sales table. Maybe that's not a realistic scenario
My SQL SERVER RETURNS one row only though i use top 2
example
CREATE TABLE #MYTABLE(COL1 INT,COL2 INT)
INSERT INTO #MYTABLE VALUES(1,5)
SELECT TOP 2 COL2 FROM #MYTABLE ORDER BY COL2
result:
5
--

>Notice that this will give a different result to my version if there is
a tie for the number 1 spot.
we have with TIE option too.

> Another one is that it sorts NULLs first, so TOP 1
in ascending order may give you a different result to MIN.
I dont agree test this:
CREATE TABLE #MYTABLE1(COL1 INT,COL2 INT)
INSERT INTO #MYTABLE1 VALUES(1,5)
INSERT INTO #MYTABLE1 VALUES(NULL,5)
INSERT INTO #MYTABLE1 VALUES(NULL,3)
INSERT INTO #MYTABLE1 VALUES(NULL,NULL)
INSERT INTO #MYTABLE1 VALUES(9,NULL)
SELECT MIN(A.COL2) FROM (SELECT TOP 2 COL2 FROM #MYTABLE1 ORDER BY COL2
desc) A
results:
3 (not null)
everything has a catches you if you dont catch it.
Regards
R.D
"David Portas" wrote:

> Notice that this will give a different result to my version if there is
> a tie for the number 1 spot.
> Also notice that your TOP query will give the wrong answer if there is
> only one row in the Sales table. Maybe that's not a realistic scenario
> in this case but it is an example of how tricky TOP is. TOP has a
> number of catches. Another one is that it sorts NULLs first, so TOP 1
> in ascending order may give you a different result to MIN.
> --
> David Portas
> SQL Server MVP
> --
>