Top Keyword of Sql Server

[shown_sunny]

Dear Intelligent Guys/Gals,

Thank u for the patience to view my Query.

I wanted to display the First 10 account no's and the last 10 account no's with this query in the table accounts.

But the below query displays only the first 10 account no's which doesn't include the last 10 account no's.


The Query is writtern in SQL Server

and the Query is

*************************************************
select A.acno from ((select top 10 acno as Acno from account) union (select top 10 acno as Acno from account order by acno desc)) as A
**************************************************

Thank U
shown sunny

[hspc]

try :

SELECT top 10 acno from account order by acno union (Select top 1 acno from account order by acno desc)

[shown_sunny]

thank u Mr hspc for u'r help.But the query after running displays
the errors
1) Incorrect syntax near the keyword 'union'
2) Incorrect syntax near the keyword 'order'

*************************************************
SELECT top 10 acno from account order by acno union (Select top 10 acno from account order by acno desc)
***************************************************

Hope u understand the errors

regards
shown sunny

[hspc]

first i'm sorry for the errors of the previous query ..
i tried it on access not on SQL server and worked fine

the sql server solution :

declare @tmp table(eid int not null,FN nvarchar(20))
insert into @tmp select top 2 employeeID , firstname from employees order by employeeID
insert into @tmp select top 2 employeeID , firstname from employees order by employeeID desc
Select * from @tmp
Go

the results :
eid FN
----------- --------------------
1 Nancy
2 Andrew
9 Anne
8 Laura
--------------------------------

I tried this on Northwind database..sure you can change the columns , table name to reflect your database.

[shown_sunny]

dear hspc,

It works fine.

But will u be able to say why it doesn't work with the query I have given to u.I didn't find anything wrong in that query.

regards
shown sunny

[hspc]

as I could understand :
the order by acno desc in your query is applied on the result of the union,
this means :
SQL server gets top 10 then gets top 10 (again) and order them in descending order.

note that union removes duplicates

[shown_sunny]

dear hspc,

The Table I am using don't have any duplicates for "acno" and the count of the table is more than 100.

when i use the query separtely as

================================
select top 10 acno from account order by acno
=================================
the result i got was First 10 account no's in the table

and for the query

=============================
select top 10 acno from account desc
=============================
the result i got was last 10 account no's in the table


If that is the result i obtained as separate queries the union should display the combined output for both the queries.Why it is not getting displayed.

I hope u can understand what I meant

shown sunny

[hspc]

Hi shown_sunny
I understansd that the table does not have duplicates..
maybe i did not explain my opint of view well.

I mean that duplicates are a result of the union itself :

select A.acno from ((select top 10 acno as Acno from account) union (select top 10 acno as Acno from account order by acno desc)) as A

select top 10 acno as Acno from account returns 1,2,3...10
then : select top 10 acno as Acno from account (the other on) executes nd gets the same results (1...10)
union merges them and removes duplicates.
order by acno sorts them descending.

so the result will be 10,9,...1

I hope I made my opinion clear :)