Query Performance

Hello SQL Gurus,
From the query below, I am using 2 TOP functions to return the desired row. I am wondering if someone can shed some light on how to AVOID using 2 TOP statements and combine into just one select query?

select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num desc

Truly Appreciate your help as this performance issue has been bugging in my head for quite some time...

Sincerely,
-Lawrence

You could write it like below:

select min(num)

from A

where num > (select min(num) from A)

-- or

select max(num) from (select top 1 num from A order by num) X

But your TOP query should perform fine if you have an index on Num column. Can you compare above queries with yours and see if there is any difference? You can compare the execution plan in query analyzer.

Query Performance

Hello SQL Champs,
From the query below, I am using 2 TOP functions to return the desired row.
I am wondering if someone can shed some light on how to AVOID using 2 TOP
statements and combine into just one select query?
select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num de
sc
Truly Appreciate your help as this performance issue has been bugging in my
head for quite some time...
Sincerely,
-LawrenceHi,
You can find yours answer here:
http://www.geocities.com/tomaszborawski/downloads.html
Just download SQL Tips Book
"Lawrence" wrote:

> Hello SQL Champs,
> From the query below, I am using 2 TOP functions to return the desired row
.
> I am wondering if someone can shed some light on how to AVOID using 2 TOP
> statements and combine into just one select query?
> select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num
desc
> Truly Appreciate your help as this performance issue has been bugging in m
y
> head for quite some time...
> Sincerely,
> -Lawrence|||Does this give you a performance issue? I've never personally run into
this problem of selecting the second row of a result set, but it looks
like the execution for such a query shouldn't be that bad. The
alternatives that immediately come to mind seem, at first thought, to
be significantly less efficient:
SELECT * FROM A WHERE EXISTS (SELECT * FROM A WHERE Num < A.Num HAVING
COUNT(*) = 1)
or
SELECT TOP 1 * FROM A WHERE Num > (SELECT MIN(Num) FROM A) ORDER BY Num|||try this.
select x.Num from A x, A y
where x.Num >= y.Num
group by x.Num
having count(*) = 2|||Well, make sure that column Num is indexed. Then the query should have
no performance problems whatsoever.
If this is not your real query, and your real query is something like
this
select top 1 * from (
select top 22111000 Num from A order by Num
) X
order by Num desc
then you have a problem, especially if Num is a wide column. Then you
might want to consider implementing a different paging mechanism.
Gert-Jan
Lawrence wrote:
> Hello SQL Champs,
> From the query below, I am using 2 TOP functions to return the desired row
.
> I am wondering if someone can shed some light on how to AVOID using 2 TOP
> statements and combine into just one select query?
> select TOP 1 * from (select TOP 2 Num from A order by Num) X order by Num
desc
> Truly Appreciate your help as this performance issue has been bugging in m
y
> head for quite some time...
> Sincerely,
> -Lawrence|||Thanks!
This works, but I think this works under the condition that the data is
pre-ordered. If data is not pre-ordered, count(*)=2 would not provide you
with the correct result. Multiple entries of x.Num would also skew the
count(*). Using Top seems more reliable.
I do greatly appreciate your help!
-Lawrence
"Omnibuzz" wrote:

> try this.
> select x.Num from A x, A y
> where x.Num >= y.Num
> group by x.Num
> having count(*) = 2