Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query to Get the 2nd Last Record from an SQL Database Table

Posted on 2010-09-05
6
Medium Priority
?
637 Views
Last Modified: 2013-12-25
I need help with an SQL Query to get Data from an SQL Server Databas. I need an SQL query which would get the 2nd last record from a table.
To explain this further if the table I need to pull data from looks like the following:

Table Name: Orders
OrderID      OrderDate      Item
1            02/09/2010      Apple
2            04/09/2010      Mango
3            05/09/2010      Orange
4            06/09/2010      Bannana

I would like my MyQuery to get me the 2nd last record that is with OrderID 3. The following query gets me the last (most recently saved record that is the one with OrderID 4 from the Orders Table.

MyQuery = "SELECT TOP 1 * FROM Orders"

I would like a modification of the above query that would get me the record with OrderID3.

I    WOULD ALSO LIKE     a modification of this query that would get me the 3rd last Record (that is the record with RecordID 2 from the this table.

Many thanks for your help. Please note I am using Visual Studio 2008


0
Comment
Question by:FaheemAhmadGul
[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
  • 3
  • 2
6 Comments
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33606374
If your MS SQL Server database version is 2005 or higher, ROW_NUMBER() function will help you to achieve your requirement like this

row_no = 2 means last 2nd record. Modify this parameter according to your requirement.

Raj
select * from
(
select *, row_number() over(order by OrderID desc) row_no
from table
) a 
where row_no = 2

Open in new window

0
 
LVL 1

Expert Comment

by:z_alex
ID: 33606392
select top 1 a.* from
(
  Select top 2 *
  from orders
) a
order by a.OrderDate desc
0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 33606446
Thank you for your comment on my request for help. Unfortunately, being  a beginner,  I have not been able to follow the solution. The exact function in which I need to put this query is as follows:

I would be grateful, if you could modify the query in the code exactly as I should put it in this function. Please note that I am sorting the Table but a Field AutoDate and I wish to get the 2nd last record ffrom this table. Please note that I am using Visual Basic and the Database is SQL Server 2005 Express Edition.

Public Function getOrder(ByVal ClientNamesID As Integer) As DataTable

        Try
            cmdSelect = New SqlClient.SqlCommand
            cmdSelect.Connection = conn

            query = "SELECT TOP 1 Item FROM Orders WHERE ClientNamesID =" & ClientNamesID & " ORDER BY AutoDate DESC"
           

            conn.Open()


            Return dc.executeQuery(query, cmdSelect, conn)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not conn.State = ConnectionState.Closed Then conn.Close()
        End Try
    End Function
Public Function getOrder(ByVal ClientNamesID As Integer) As DataTable

        Try
            cmdSelect = New SqlClient.SqlCommand
            cmdSelect.Connection = conn

            query = "SELECT TOP 1 Item FROM Orders WHERE ClientNamesID =" & ClientNamesID & " ORDER BY AutoDate DESC"
            

            conn.Open()


            Return dc.executeQuery(query, cmdSelect, conn)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not conn.State = ConnectionState.Closed Then conn.Close()
        End Try
    End Function

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 600 total points
ID: 33606456

Public Function getOrder(ByVal ClientNamesID As Integer) As DataTable

        Try
            cmdSelect = New SqlClient.SqlCommand
            cmdSelect.Connection = conn

--            query = "SELECT TOP 1 Item FROM Orders WHERE ClientNamesID =" & ClientNamesID & " ORDER BY AutoDate DESC"

			query = "select * from (select *, row_number() over(order by OrderID desc) row_no " & _
				" from Orders where ClientNamesID =" & ClientNamesID & ") a where row_no = 2"
            

            conn.Open()


            Return dc.executeQuery(query, cmdSelect, conn)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            If Not conn.State = ConnectionState.Closed Then conn.Close()
        End Try
    End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:FaheemAhmadGul
ID: 33606506
Many thanks. I am very grateful. Regards
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33606509
I love to hear, you got your solution :)

All the best
Raj
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

722 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