Solved

selecting the 2nd highest record?

Posted on 2008-10-14
4
232 Views
Last Modified: 2012-05-05
I need to get the 2nd latest value of my OrderId field.
The code I am using is attached.
Could you pleade tell me why this is a syntax error?

select top 1 OrderID from (select top 2 OrderID from 
T_orderheader order by OrderID desc) 
order by OrderID asc

Open in new window

0
Comment
Question by:billyleo
4 Comments
 
LVL 8

Accepted Solution

by:
tiagosalgado earned 500 total points
ID: 22710203
Give a name to your "table".

select top 1 OrderID from (select top 2 OrderID from 
T_orderheader order by OrderID desc) l
order by OrderID asc

Open in new window

0
 
LVL 31

Expert Comment

by:James Murrell
ID: 22710205
something like

SELECT TOP 1 *
FROM (SELECT TOP 2*
FROM tablename
ORDER BY columnname DESC)e1
ORDER BY Sal
0
 
LVL 5

Expert Comment

by:muktajindal
ID: 22710258
You may use following query instead :

SELECT MAX(OrderID)
FROM T_orderheader
WHERE OrderID NOT IN
  (SELECT MAX(OrderID)
   FROM T_orderheader)
0
 

Author Closing Comment

by:billyleo
ID: 31505827
Cool, Thx!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
best counters for cpu high usage 3 33
How to share SSIS Package? 6 37
C# LINQ ForEach() question 6 28
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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.

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