Solved

selecting the 2nd highest record?

Posted on 2008-10-14
4
234 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
[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
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 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