Solved

VB.net: SQL Opposite of SELECT TOP

Posted on 2013-01-22
10
618 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:Murray Brown
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 143

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:Murray Brown
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:Murray Brown
ID: 38804759
Thanks. That does actually work. Sorry about the confusion
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

717 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