Thank u for the patience

[shown_sunny]

dear hspc,


I Thank u again for the patience to view my question.


I forgot to tell u the result i got after running the queries separately.

For the query
=======================
select top 10 acno from account
=======================

I got the result as
1,2.....10

and for the query
====================================
select top 10 acno from account order by acno desc
=====================================

I got the result as
20,19,18....11

Now I hope u would understand why i am sticking to what i say.That is why I am asking u the same question again and again.If i had explained with an example what i got as the result it could not have made problems for u.



regards
shown sunny

[hspc]

I understand why you are confused about the results :)
yes.. running the two queries separately will give the results you send .. but this is not how SQL erver thinks about it !!!

what you intend to to is :
1-get first ten
2-get last ten (order by ... desc)
3-union them

what SQL server understands :
1-get fisrt ten
2-get first ten (again)
3-union them and remove duplicates.
4-sort them (order by ... desc)

this is the idea .. you bealive that order by acno desc should be applied on the second part of the query (i don't belame you for this) but SQL server applies it on the whole result of the union.

[shown_sunny]

dear hspc,

Ok Now i understand it clearly.

But will u be able to tell me is it possible to get the result what i intended by the same method as I described ie., by using Union.


Because I was actually experimenting with TOP keyword.Although I have worked in Oracle and PostgreSQL,I haven't worked much in SQL SERVER.


Thank u again
shown_sunny

[hspc]

you are welcome shown_sunny..
unfortunatetly I couldn't solve it your way..:confused:
so till now the solution i posted (about using a table variable) is the best one.
but if you find another solution then please post it :)

[shown_sunny]

thanks hspc,


I am carrying on with my experimentation.I will post it if i get an answer for the same.

Hoping to see u later.

shown sunny

[shown_sunny]

dear hspc,

I got it right with the following query.

It worked perfectly well.My experimentation paid off.

and this was the query

=============================================

(select A.acno from (select top 2 acno from account ) as A) union (select B.acno from (select top 2 acno from account order by acno desc) as B)

eg: Result
ARG01
AZB01
VZ001
vz002
==============================================

The following query
=========================================
select A.acno from (select top 2 acno from account ) as A
=========================================

got the result

ARG01
AZB01

and the query
==============================================
select B.acno from (select top 2 acno from account order by acno desc) as B
==============================================

got the result

VZ001
vz002

I hope it is clear to u.

Thank u very much for u'r reply to my Questions

regards,
shown sunny