• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 678
  • Last Modified:

VB.net: SQL Opposite of SELECT TOP

Hi

What is the opposite of SELECT TOP in SQL
I need to only select eg the bottom/last 50 records added to my table
0
Murray Brown
Asked:
Murray Brown
3 Solutions
 
CluskittCommented:
Use select top anyway, but reverse the order. That is:
SELECT TOP 50 FieldName
FROM TableName
ORDER BY FieldName

to reverse:
SELECT TOP 50 FieldName
FROM TableName
ORDER BY FieldName DESC
0
 
sognoctCommented:
in addiction to cluskitt
just in case you want the bottom 50 ordered as they want the top 50

select *
from
(
select top 50 * from TableName order by FieldName desc
) t1
order by FieldName
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Thanks.
This is an automated system where any SQL statement can be used so the Order By part
may not be known. Is there a way to make provision for this?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
possibly by using a VIEW instead of the table itself, which  returns the data as needed...
0
 
CluskittCommented:
The only way I can see to do this would be dumping the SQL into a temp table, then delete COUNT(*)-50.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. I tested the Order By method and I don't think that this works
If have Select Top 10 * From Table1 then this selects the first 10 records
If you then Select * From(Select Top 10 * From Table1) Order By ID
this just selects the same top 10 but just puts them in reverse order.
Or am I confused?
0
 
deightonCommented:
I'm not sure what you mean exactly when you say '...automated system where any SQL statement can be used...'

Are users sending their own SQL to run?  Do you have a way of determining via sorting what the last  records added are, e.g. time stamp or increasing index field?  If you don't have such a way of ordering, then 'SELECT ' wouldn't guarantee to return records in any particular sequence.

you could do sometking like

WITH BOTTOM50 as (SELECT TOP 50 * FROM YourTable ORDER BY TimeStamp DESC)
SELECT * FROM BOTTOM50 ORDER BY AnyField   --any SQL you like on the second row

Open in new window

0
 
CluskittCommented:
What you wanted to do was:
Select * From(Select Top 10 * From Table1 Order by ID DESC) Order By ID
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks. That does actually work. Sorry about the confusion
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now