Min function

[hanifa]

Hi guys,
I have this situation..
The Min funcition in SQL returns lowest value in query but I what to return is the 2nd lowest instead.
Take this table for example col=Pay(3000,2300,1000,1900,1200)
Min(pay)=$1000 but I want to return Min2ndlowest(pay)=1200
Is there a way to solve this problem.

Thanks for help rendered..

[DavidRyan]

Hi guys,
I have this situation..
The Min funcition in SQL returns lowest value in query but I what to return is the 2nd lowest instead.
Take this table for example col=Pay(3000,2300,1000,1900,1200)
Min(pay)=$1000 but I want to return Min2ndlowest(pay)=1200
Is there a way to solve this problem.

Thanks for help rendered..

If you say the table before running the min function is x. Run the min function and remove the result from x, call this table y. Then run min again on y, this will return the lowest result from y, which is also the lowest result from x.

[hanifa]

Hi,
Below stated is my original query from my database but for the sake of simplicity , I have used the pubs database to convey my thoughts.

select p.pub_name,min(t.price),max(t.price) from publishers p,titles t
where
p.pub_id=t.pub_id
group by p.pub_name

David thanks for the reply and although I understand wat you are saying, I kinda find it pretty hard to think of it in SQL. Is there a way to manipulate the Min function so as to change it . Or is it somehow possible to rewrite another function like Min2ndLowest() to easily solve this situation.I will be more happy to solve via this route as I later have to solve other queries like Max2ndHighest() and so forth.
Or is it only possible thru some serious query design:sad:

Thanks for help guys..

My Original Query

select b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported,min(t.begintime),max(t.begintime)
from
batches b, queues q,tasks t
where
b.queueid=q.queueid
and
b.batchid = t.batchid
group by b.batchid,b.batcharchname,b.realpagecnt,b.queueid,q.queuename,b.isexported

[jonamthomas]

In sybase / even within a stored proc, I would do this

set row count 1
select pay from paytable where pay not in (select min(pay) from paytable) order by pay