Solved

VB.net: SQL Opposite of SELECT TOP

Posted on 2013-01-22
10
583 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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now