SQL query - limiting records to one rather than all instances

Hi,

Thank you for taking a look at my question!!  your help is most appreciated.

Below is the result of my SQL query, but I only want the row with the highest value in the last column (in this case the value is 79)  I have also put in the query below that, could someone please help me see what is wrong.  Do I perhaps need a join in the query?

281005      System      NOT FUNDED      21
281005      System      NOT FUNDED      22
281005      System      NOT FUNDED      41
281005      System      NOT FUNDED      43
281005      System      NOT FUNDED      45
281005      System      NOT FUNDED      46
281005      System      NOT FUNDED      47
281005      System      NOT FUNDED      48
281005      Jessica O NOT FUNDED      79

$queryerr3 = "SELECT  LF.[LoanNo]
      ,LF.[Lst_Desc]
      ,LF.[Sta_Desc]
      ,LF.[Lon_DateCreated]
      ,LF.[Fnd_Desc]
      ,LF.[Rsk_Name]
      ,LS.[Los_ToStageID]
      ,LS.[Usr_Alias]
  FROM DBQ as LF,DBLSTAGE as LS
  Where LF.[LoanID]=LS.[Los_LoanID]
  and (LF.Lst_Desc like 'Application' or LF.Lst_Desc like 'Cancelled' or LF.Lst_Desc like 'Declined' or LF.Lst_Desc like 'Underwriting')
  and LF.Lon_DateFunded is not null";
mavmanauNetwork Engineer/SysadminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

santhimurthydCommented:
check the querry, but replace <<Column Name>>  in querry with the name of the column, where your getting the last column in your result set sample

$queryerr3 ="SELECT Top 1 LF.[LoanNo]
      ,LF.[Lst_Desc]
      ,LF.[Sta_Desc]
      ,LF.[Lon_DateCreated]
      ,LF.[Fnd_Desc]
      ,LF.[Rsk_Name]
      ,LS.[Los_ToStageID]
      ,LS.[Usr_Alias]
  FROM DBQ as LF,DBLSTAGE as LS
  Where LF.[LoanID]=LS.[Los_LoanID]
  and (LF.Lst_Desc like 'Application' or LF.Lst_Desc like 'Cancelled' or LF.Lst_Desc like 'Declined' or LF.Lst_Desc like 'Underwriting')
  and LF.Lon_DateFunded is not null
Order by <<Column Name>> DESC";
0
mavmanauNetwork Engineer/SysadminAuthor Commented:
what about if there are multiple loanno's appearing though, I would in that case need 2 to appear ie

281005      System      NOT FUNDED      21
281005      System      NOT FUNDED      22
281005      System      NOT FUNDED      41
281005      System      NOT FUNDED      43
281005      System      NOT FUNDED      45
281005      System      NOT FUNDED      46
281005      System      NOT FUNDED      47
281005      System      NOT FUNDED      48
281005      Jessica O NOT FUNDED      79
281006      System      NOT FUNDED      21
281006      System      NOT FUNDED      22
281006      System      NOT FUNDED      41
281006      System      NOT FUNDED      43
281006      System      NOT FUNDED      45
281006      System      NOT FUNDED      46
281006      System      NOT FUNDED      47
281006      System      NOT FUNDED      48
281006      J O NOT FUNDED      79

would appear with only:
281005      Jessica O NOT FUNDED      79

but I would need:
281005      Jessica O NOT FUNDED      79
281006      J O NOT FUNDED      79
0
santhimurthydCommented:
Below Querry will work and try for better solution

$queryerr3 ="SELECT LF.[LoanNo]
      ,LF.[Lst_Desc]
      ,LF.[Sta_Desc]
      ,LF.[Lon_DateCreated]
      ,LF.[Fnd_Desc]
      ,LF.[Rsk_Name]
      ,LS.[Los_ToStageID]
      ,LS.[Usr_Alias]
  FROM DBQ as LF,DBLSTAGE as LS
  Where LF.[LoanID]=LS.[Los_LoanID]
  and (LF.Lst_Desc like 'Application' or LF.Lst_Desc like 'Cancelled' or LF.Lst_Desc like 'Declined' or LF.Lst_Desc like 'Underwriting')
  and LF.Lon_DateFunded is not null
and <<Column Name>> = ( select Top 1 <<Column Name>>
FROM DBQ as LF,DBLSTAGE as LS
  Where LF.[LoanID]=LS.[Los_LoanID]
  and (LF.Lst_Desc like 'Application' or LF.Lst_Desc like 'Cancelled' or LF.Lst_Desc like 'Declined' or LF.Lst_Desc like 'Underwriting')
  and LF.Lon_DateFunded is not null);
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ralmadaCommented:
try the below. Just replace "yourcolumn" with the name of your column storing the values 79, 21, etc.


select * from (
	SELECT  LF.[LoanNo]
	      ,LF.[Lst_Desc]
	      ,LF.[Sta_Desc]
	      ,LF.[Lon_DateCreated]
	      ,LF.[Fnd_Desc]
	      ,LF.[Rsk_Name]
	      ,LS.[Los_ToStageID] 
	      ,LS.[Usr_Alias]
	      ,row_number() over (partition by LoanNo order by yourcolumn desc) rn
	FROM DBQ as LF,
	INNER JOIN DBLSTAGE as LS ON LF.[LoanID]=LS.[Los_LoanID]
	WHERE LF.Lst_Desc in ('Application', 'Cancelled', 'Declined', 'Underwriting')
	  and LF.Lon_DateFunded is not null"
) a
where rn = 1

Open in new window

0
ralmadaCommented:
oops, typo there
$queryerr3 ="select * from (
	SELECT  LF.[LoanNo]
	      ,LF.[Lst_Desc]
	      ,LF.[Sta_Desc]
	      ,LF.[Lon_DateCreated]
	      ,LF.[Fnd_Desc]
	      ,LF.[Rsk_Name]
	      ,LS.[Los_ToStageID] 
	      ,LS.[Usr_Alias]
	      ,row_number() over (partition by LoanNo order by yourcolumn desc) rn
	FROM DBQ as LF,
	INNER JOIN DBLSTAGE as LS ON LF.[LoanID]=LS.[Los_LoanID]
	WHERE LF.Lst_Desc in ('Application', 'Cancelled', 'Declined', 'Underwriting')
	  and LF.Lon_DateFunded is not null
) a
where rn = 1";

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ralmadaCommented:
ok another typo


$queryerr3 ="select * from (
	SELECT  LF.[LoanNo]
	      ,LF.[Lst_Desc]
	      ,LF.[Sta_Desc]
	      ,LF.[Lon_DateCreated]
	      ,LF.[Fnd_Desc]
	      ,LF.[Rsk_Name]
	      ,LS.[Los_ToStageID] 
	      ,LS.[Usr_Alias]
	      ,row_number() over (partition by LF.LoanNo order by yourcolumn desc) rn
	FROM DBQ as LF,
	INNER JOIN DBLSTAGE as LS ON LF.[LoanID]=LS.[Los_LoanID]
	WHERE LF.Lst_Desc in ('Application', 'Cancelled', 'Declined', 'Underwriting')
	  and LF.Lon_DateFunded is not null
) a
where rn = 1";

Open in new window

0
mavmanauNetwork Engineer/SysadminAuthor Commented:
awesome...thank you guys, I shall play with it now!!
0
mavmanauNetwork Engineer/SysadminAuthor Commented:
Thank you very much for your assistance!! very much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.