MS SQL (min function To min2ndlowest)

Hi,
Below stated is my original query from my database but for the sake ofsimplicity , 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

Instead of the min(Price), I would like to get the min2ndLowest(Price).
Is there away to manipulate the Min function so as to change it . Or is itsomehow possible to rewrite another function like Min2ndLowest() toeasily solve this situation.I will be more happy to solve via thisroute as I later have to solve other queries like Max2ndHighest() andso forth.
Or is it only possible thru some serious query design

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

--SELECT MIN(b.PRICE1) AS Max2ndHighest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1 DESC) AS b

--SELECT MAX(b.PRICE1) As MIN2ndLowest FROM (SELECT TOP (2) a.PRICE1 FROM my_x a WHERE a.PRICE1 is not NULL order by a.PRICE1) AS b

--table is from this thread with more data:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619492&SiteID=1

create

table #x

(

ProductID

int,

MarketID

int,

Date

datetime,

PRICE1

decimal(9,2),

PRICE2

decimal(9,2),

PRICE3

decimal(9,2)

)

insert

#x

select

1, 2,'1/01/2006', 2.78, 3.4, 2.97unionall

select

1, 2,'2/01/2006', 2.51, 3.5,NULLunionall

select

1, 2,'3/01/2006',NULL, 3.6,NULLunionall

select

1, 2,'4/01/2006',NULL, 3.55,NULLunionall

select

2, 4,'1/01/2006', 3.44, 1.23, 4.33unionall

select

2, 4,'2/01/2006', 3.55, 1.22, 4.22unionall

select

2, 4,'3/01/2006', 3.13, 1.51, 4.54unionall

select

2, 4,'4/01/2006',NULL, 1.50, 4.25

SELECT b.ProductID, b.MarketID,

(

SELECTMIN(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDORDERBY a.PRICE2DESC)AS c)AS Max2ndHighest,

(

SELECTMAX(c.PRICE2)FROM(SELECTTOP(2) a.PRICE2FROM #x aWHERE a.PRICE2isnotNULLAND b.ProductID=a.ProductIDAND b.MarketID=a.MarketIDorderby a.PRICE2)AS c)AS MIN2ndLowest

FROM

#xAS b

GROUP

BY b.ProductID, b.MarketID

drop

table #x|||

Insert this into the joins area of your original query:

LEFT JOIN ({a complete copy of your original query}) t1 ON ({field1 from original query}=t1.{field1 from original query} AND {field2}=t1.{field2} ... AND {field to be min-ed from original query}={min-ed field result from subquery})

Then add

WHERE t1.{field1} IS NULL to the where clause of your original query.

For example:

SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1

becomes

SELECT field1,MIN(field2) AS MinField2
FROM table1
LEFT JOIN (
SELECT field1,MIN(field2) AS MinField2
FROM table1
WHERE field3='something'
GROUP BY field1) t1 ON (table1.field1=t1.field1 AND table1.field2=t1.MinField2)
WHERE field3='something'AND t1.field1 IS NULL
GROUP BY field1