September 15, 2004 at 3:22 am
Hi
Im just wondering how to program a stored procedure to get the second highest numerical value stored in a column in a table
Thanks in advance for any help!!!
September 15, 2004 at 4:57 am
Ugly - But it works
create table tmp_tmp_tmp
(id int)
insert into tmp_tmp_tmp values (1)
insert into tmp_tmp_tmp values (2)
insert into tmp_tmp_tmp values (3)
insert into tmp_tmp_tmp values (4)
select max(id) from tmp_tmp_tmp
where id <> (select max(id) from tmp_tmp_tmp)
September 15, 2004 at 6:25 am
there is one more way...
select top 1 id from (
select top 2 id from tmp_tmp_tmp order by id desc)
as tmp order by id
September 15, 2004 at 6:58 am
Just a couple more where the nth record can be picked:
DECLARE @var1 VARCHAR(12)
DECLARE @var2 VARCHAR(30)
SET ROWCOUNT 10
SELECT @var1 = id, @var2 = firstname FROM users ORDER BY id
SET ROWCOUNT 0
SELECT TOP 3 * FROM users
WHERE (id >= @var1 AND firstname=@var2) OR ( firstname>@var2)
ORDER BY firstname,id
DECLARE @var1 VARCHAR(12)
DECLARE @var2 VARCHAR(30)
SET ROWCOUNT 10
SELECT * FROM users ORDER BY id
or
Select * From Users u1 Where
(10-1) = (Select Count(u2.id) From users u2 Where u2.id > u1.id)
September 16, 2004 at 7:13 am
Yet another way:
use Northwind
go
Select top 2 rownum = identity(int,1,1),
productid, quantity
into #tmp
from [order details]
order by quantity desc
select * from #tmp
where rownum = 2
drop table #tmp
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
September 16, 2004 at 10:47 am
select max(colName) from table
where colName not in ( select max(colName) from table)
The subquery gets the max value and then the main query get the next value.
September 16, 2004 at 3:19 pm
Yet another. Its a single pass without a sort step.
declare @MaxNum int
declare @MaxNumMinOne int
set @MaxNum = 0
set @MaxNumMinOne = -1
SELECT @MaxNumMinOne = case when @MaxNum < ClientNumber then @MaxNum else @MaxNumMinOne end
, @MaxNum = case when @MaxNum < ClientNumber then ClientNumber else @MaxNum end
@MaxNum then ClientNumber else @MaxNumMinOne end
FROM MyTable
print @MaxNum
print @MaxNumMinOne
September 22, 2004 at 3:58 am
You can try this query:
select max(resourceid) from resources
where resourceid < (select max(resourceid) from resources)
The subquery selects all the values lower than the max value and the main query picks the highest of the lot.
Hope this helps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply