Re: I need a query

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/13/04 

Date: Sat, 13 Mar 2004 20:20:06 +0530

hi mehran,

>>SELECT TOP 20 FROM MONEY<<

top clause is meaningless if not specified with the order by clause.
Otherwise retrieval order of the row is not guaranteed and any row will be
retrieved from the table.

There are various methods to accomplish what you are looking for.

Following example will retrieve the top 20 orders on the basis of the amount
of frieght. (im trying to show you the importance of "order by" clause to
get the meaningful result.)

select top 20 orderid,freight from orders
order by freight desc

--following query will give you the rows from 20 to 40 on the basis of
freight amount.

select top 20 orderid,freight from orders
where orderid not in
(select top 20 orderid from orders o
 order by freight desc)
order by freight desc

having said that, it is important to know that you can not use variable in
the top clause. ie, if you try something as follows, it will error out.

declare @top int
set @top=20
select top @top orderid,freight from orders
order by freight

There are many workarounds to this. I will show you 2 of them below.

--(1)
declare @from int,@to int
select @from=20, @to=25

select orderid,freight from
(select orderid,
       freight,
(select count(distinct freight) from orders a
 where a.freight >= b.freight) rnk
from orders b) X
where rnk > @from
      and rnk <= @to
order by rnk

--(2)dynamic sql

declare @from int,@to int, @final_row int
select @from=20, @to=25, @final_row = @to - @from

exec ('select top '+ @final_row + ' orderid,freight from orders
       where orderid not in
       (select top ' + @from + 'orderid from orders o
        order by freight desc)
        order by freight desc') 

--
Vishal Parkar
vgparkar@yahoo.co.in

Relevant Pages

  • Text to Int
    ... I'd like to convert text to int to retrieve bad records. ... However I am getting a data type mismatch error. ... How can I get it to work in the where clause? ...
    (microsoft.public.access.queries)
  • Re: Record source at runtime
    ... Clause, but is in the data requested, so it will return the PKs ... retrieve indexes for Lastname and Firstname. ... had I written the query: ...
    (microsoft.public.access.formscoding)
  • Re: strange thing about the SQL method
    ... Ron Weiner wrote: ... > clause when we retrieve the data. ... the PK if one exists (don't mention 'clustered index': ...
    (microsoft.public.access.modulesdaovba)
  • Re: What does it mean to SET a value?
    ... a WHERE clause the variable is left undefined and an error generated: ... DECLARE @MyFrt MONEY; ... Freight //A table. ...
    (comp.databases.ms-sqlserver)
  • Re: Sql server Views!
    ... You should put a filter in the WHERE clause that defines your view so only ... They are just a glorified SQL command that retrieve whatever ... > I have got copies of the SQL database and their tables. ... > FOREVER to open and it opens in a strange way. ...
    (microsoft.public.fox.helpwanted)