what is the opposite of top 5

[aspnet98]

what is the opposite of top 5?
I use top 5 to return 5 out of 16. i need to return the opposite, in this case 11 records.

if top 5 of 20, then the 15 records...

How would I get this?

 

[DotNetGnat]

SELECT TOP 5 products from mytable order by productId desc

will return top 5 rows with productId's in the desc order

you can do

ECT TOP 5 products from mytable order by productId [red]asc[/red]

this to get in the reverse order...

-DNG

 

[DotNetGnat]

oops...copy paste typos...

it should be SELECT TOP 5 in the second query...obvious typo...

-DNG

 

[amrita418]

Top command limits the number of results in the output. The order by clause orders the output by the given columns.

For example

create table #Test (ID int, Name varchar(10))

insert into #Test values (1, 'Test')
insert into #Test values (2, 'Test1')
insert into #Test values (3, 'Test2')
insert into #Test values (4, 'Test3')
insert into #Test values (5, 'Test4')

select   top 3 
         id, 
         name
from     #Test a
order by id

Try something like this to filter out the top 3 and print the remaining.

select   id, 
         name
from     #Test a
where    (select   count(*) 
          from     #Test b 
          where    b.id <= a.id) > 3

Regards,
AA

 

[DotNetGnat]

ok...the query expert speaks...may be thats what you want to do...

i wish i could write queries like amrita418...

-DNG

 

[vongrunt]

Assuming you have primary key and explicit ORDER BY (as it should be):

select *
from myTable
where primarykey not in
(   select top 5 primarykey 
    from myTable
    order by something
) 
order by something

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[aspnet98]

thanks!