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?
 
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.