Solved

VB.net: SQL Opposite of SELECT TOP

Posted on 2013-01-22
10
587 Views
Last Modified: 2013-01-22
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
Comment
Question by:murbro
10 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 167 total points
ID: 38804459
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
 
LVL 9

Accepted Solution

by:
sognoct earned 167 total points
ID: 38804469
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
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 166 total points
ID: 38804472
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:murbro
ID: 38804500
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38804510
possibly by using a VIEW instead of the table itself, which  returns the data as needed...
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38804529
The only way I can see to do this would be dumping the SQL into a temp table, then delete COUNT(*)-50.
0
 

Author Comment

by:murbro
ID: 38804580
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
 
LVL 18

Expert Comment

by:deighton
ID: 38804591
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38804745
What you wanted to do was:
Select * From(Select Top 10 * From Table1 Order by ID DESC) Order By ID
0
 

Author Closing Comment

by:murbro
ID: 38804759
Thanks. That does actually work. Sorry about the confusion
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question