Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
Solved
Select statement that will only give me the latest (max) date where multiple records exist for each ID
Posted on 2012-12-28
I have a table that shows multiple records per ID, it tracks the status of a loan in process and many times people will record the same status more than one time. I'm looking for a select statement that will give me just the latest date that the loan was moved to it's current status. So in other words the max date for the max status but I can't use an aggregate function like (MAX).
for example my current select statement;
SELECT DISTINCT
TOP (100) PERCENT dbo.setups_loanstatus.stat
dbo.loan_status.datefirste
FROM dbo.loan_main INNER JOIN
dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.stat
dbo.loan_status ON dbo.setups_loanstatus.stat
dbo.loan_main.loanrecordid
ORDER BY dbo.loan_main.loanid DESC, dbo.setups_loanstatus.stat
gives me the correct date for the current status but if the loan has been in that status more than once I get two records for that Loan ID.
for example my current select statement;
SELECT DISTINCT
TOP (100) PERCENT dbo.setups_loanstatus.stat
dbo.loan_status.datefirste
FROM dbo.loan_main INNER JOIN
dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.stat
dbo.loan_status ON dbo.setups_loanstatus.stat
dbo.loan_main.loanrecordid
ORDER BY dbo.loan_main.loanid DESC, dbo.setups_loanstatus.stat
gives me the correct date for the current status but if the loan has been in that status more than once I get two records for that Loan ID.
[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-
3
6 Comments
Try using ROW_NUMBER()
;WITH CTE AS (
SELECT DISTINCT
TOP (100) PERCENT dbo.setups_loanstatus.stat
dbo.loan_status.datefirste
ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
FROM dbo.loan_main INNER JOIN
dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.stat
dbo.loan_status ON dbo.setups_loanstatus.stat
dbo.loan_main.loanrecordid
SELECT
*
FROM
CTE
WHERE
RN = 1
;WITH CTE AS (
SELECT DISTINCT
TOP (100) PERCENT dbo.setups_loanstatus.stat
dbo.loan_status.datefirste
ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
FROM dbo.loan_main INNER JOIN
dbo.setups_loanstatus ON dbo.loan_main.statusid = dbo.setups_loanstatus.stat
dbo.loan_status ON dbo.setups_loanstatus.stat
dbo.loan_main.loanrecordid
SELECT
*
FROM
CTE
WHERE
RN = 1
Ok, that works but (I didn't mention this before, didn't think it matters) I'm using the select statement for a view and when I use that text in the view it crashes SQL management studio, any way it can be done to allow use in a view?
try it without the CTE like this.
SELECT
*
FROM
(
SELECT DISTINCT TOP (100) PERCENT
dbo.setups_loanstatus.statusdescription AS [Latest Status]
,dbo.loan_main.loanid
,dbo.setups_loanstatus.statusorder
,dbo.loan_status.statusid
,dbo.loan_status.datefirstentered
,dbo.loan_main.lenderdatabaseid
,dbo.loan_main.loanrecordid
,ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
FROM
dbo.loan_main
INNER JOIN dbo.setups_loanstatus
ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid
LEFT OUTER JOIN dbo.loan_status
ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid
AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid
AND dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
) a
WHERE
RN = 1
Try creating it without using the GUI.
CREATE VIEW [dbo].[YourViewName]
AS
SELECT
*
FROM
(
SELECT DISTINCT TOP (100) PERCENT
dbo.setups_loanstatus.statusdescription AS [Latest Status]
,dbo.loan_main.loanid
,dbo.setups_loanstatus.statusorder
,dbo.loan_status.statusid
,dbo.loan_status.datefirstentered
,dbo.loan_main.lenderdatabaseid
,dbo.loan_main.loanrecordid
,ROW_NUMBER() OVER (PARTITION BY LoanID, StatusID ORDER BY DateFirstEntered DESC) RN
FROM
dbo.loan_main
INNER JOIN dbo.setups_loanstatus
ON dbo.loan_main.statusid = dbo.setups_loanstatus.statusid
LEFT OUTER JOIN dbo.loan_status
ON dbo.setups_loanstatus.statusid = dbo.loan_status.statusid
AND dbo.loan_main.lenderdatabaseid = dbo.loan_status.lenderdatabaseid
AND dbo.loan_main.loanrecordid = dbo.loan_status.loanrecordid
) a
WHERE
RN = 1
GO
Featured Post
Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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.
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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses
Course of the Month14 days, left to enroll
656 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.