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
Solved

VB.net: SQL Opposite of SELECT TOP

Posted on 2013-01-22
10
593 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 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: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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

828 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