Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Query help

Posted on 2010-01-07
Medium Priority
Last Modified: 2012-05-08
I have a table with ID's and transaction dates.  I want to get the second highest date and if there isn't a second highest date then i want the main record.

I have:

row_number() over(partition by id order by id, historydate desc) as rownumber,
from tblTable

Now, that i have the row number, i want all the records where it equals 2 and if an id does not have a 2, then i want the number 1 record.

Question by:catwalk
1 Comment
LVL 10

Accepted Solution

lof earned 2000 total points
ID: 26205994

select *
from (
      select *, ROW_NUMBER() over(partition by id order by id rownumber desc) rownum2
      from (
            row_number() over(partition by id order by id, historydate desc) as rownumber,
            from tblTable
      ) NumberedRecords
      where rownumber<=2
) T
where rownum2=1

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

580 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