• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Using RANK in a UNION All query, then ordering the results

I have a query that I can get to work for one parameter, but when I enter a different parameter, it doesn't order the results correctly.  I know I'm just missing something, but am blocked!  I'm attaching the query, any help will be greatly appreciated.
SQLQuery47.sql
0
Sherry
Asked:
Sherry
  • 8
  • 6
  • 2
  • +1
1 Solution
 
LowfatspreadCommented:
ok so what is the actual problem...

the rank() function will give the rank value relative to the query within which its contained...

where you wanting to get the rank across the union?
or somethink else?
0
 
SherryDeveloperAuthor Commented:
The order in which the items are in the final results need to be:

debt
payment (if any)
next debt
 and so on

I'm attaching 2 sets of results, hope this helps.
Results-1.docx
0
 
LowfatspreadCommented:
no its not at all clear ... explain what the 2 results represent and which if any is correct.?

you havn't explained what the problem with rank() is ...

you don't appear to actually use it in the final query ...
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
SherryDeveloperAuthor Commented:
The first result set is the correct one.  The rank seems to work fine.  When I use the rank in the Order by one result set is correct, the other isn't.  Not using it, the other result set is correct.   What I can't figure out is why it works for one DOC # and not the other.
0
 
PortletPaulfreelancerCommented:
The ranking is partitioned by IBS_DOC_NUMBER and ORDER BY IBS_DEBT_TYPE_CODE ASC, IBS_DEBT_DATE ASC

The output (results-1.docx) does not display IBS_DOC_NUMBER

the output order requested is: ORDER BY D.IBS_Type,D.IBS_Date;
and this appears to be true (unless I missed something)

I think IBS_DOC_NUMBER may be changing when you don't expect it to - but you cannot see that field in the output
0
 
SherryDeveloperAuthor Commented:
I put the doc_number and name back in and it didn't change anything.  I still get one result set correct the other not.  It just adds the column for each to each line in the results.  I also tried changing the ranking so that it's partitioned by the debt_type_code in place of the doc_number.
0
 
PortletPaulfreelancerCommented:
sorry was correcting my previous post - you are selecting by a single IBS_DOC_NUMBER so it's not that - sorry.

But can you be more specific about what is wrong in the output? which rows are "wrong"
0
 
SherryDeveloperAuthor Commented:
See attached.  I've highlighted the rows that are in the wrong order on the second set of results.  I'm gone until tomorrow morning.  Back in at 7:30 Pacific time.  I really appreciate you trying to help with this.  It's hard when you don't have access to the data I'm trying to query. :)
Results-1.docx
0
 
PortletPaulfreelancerCommented:
ok, thanks. I guess that means:

ORDER BY D.DebtPayID, D.IBS_Type,D.IBS_Date;

but note, this would change the previously "correct" results
Q-28022403.xlsx
0
 
deightonprogCommented:
rank() gives a ranking, but not an order, so if you had 3 dates and gave them a rank() in a query, you might see

date                  rank
01/01/2000         2
01/01/1950         1
01/01/2010         3

if you wanted to see them in order - 'Select date from table order by date'

date                  
01/01/1950
01/01/2000        
01/01/2010        

If my understanding is right here, you just need to order by what you want, or at least include the ranking number you calculate in your ORDER BY

I have to say it isn't totally clear what you want - you obviously want the rows to appear sorted into a particular order - if that can be written down and is consistent, then it should be possible to do it.
0
 
SherryDeveloperAuthor Commented:
Per you response with the spreadsheet.  That is exactly my problem.  I can get one result set to come out correct, but not both.  Is there something other than ranking the two tables in the union that would work?  The business is picky on how they want this sorted.  The first query/tables hold the debts, the second has the debt payments and also has the debt_type, debt_reference and debt_date to join the debts to the payments.  So they want the debts followed by any payment for that debt, then the next debt.
0
 
PortletPaulfreelancerCommented:
Hi, I feel sure a sort order can be resolved - but I think there has to be a field in common between the debt and the payment that isn't being gathered into your temporary table.

I'd like to know if the following join structure is feasible, and if so how DP joins to D

select <<whatever>>
FROM TB_IBS_DEBT D
LEFT OUTER JOIN TB_IBS_DEBT_PAY DP ON D.<<what ??>> = DP.<<what ??>>

Open in new window

0
 
SherryDeveloperAuthor Commented:
Sorry I didn't get back to you sooner.  Been sick.     I'm joining the two tables into the temp by the IBS_Debt_Type, IBS_Debt_Ref and IBS_Debt_Date as shown below.

--=======================================================================================
CREATE TABLE #TEMP_DEBT
(  DOC_NB NUMERIC(10,0), IBS_Type CHAR(2),IBS_Description VARCHAR(20),IBS_Date DATE,
            IBS_LOC CHAR(3), IBS_REF CHAR(6), Amount NUMERIC(10,2), IBS_Batch CHAR(3),
            IBS_Item CHAR(4),Balance_Due NUMERIC(10,2),DebtPayID INT
)

INSERT INTO #TEMP_DEBT
(  DOC_NB, IBS_Type,IBS_Description,IBS_Date,IBS_LOC,IBS_REF,Amount,IBS_Batch
            ,IBS_Item,Balance_Due,DebtPayID
)
      
(      SELECT
                  D.IBS_DOC_NUMBER AS DOC_NB
            ,      D.IBS_DEBT_TYPE_CODE AS IBS_Type
            ,      DT.DEBT_TYPE_DESCRIPTION  AS IBS_Description
            ,      D.IBS_DEBT_DATE AS IBS_Date
            ,      D.IBS_DEBT_LOCATION AS IBS_LOC
            ,      D.IBS_DEBT_REFERENCE  AS IBS_REF
            ,      D.IBS_DEBT_AMOUNT AS Amount
            ,      D.IBS_BATCH_NUMBER AS IBS_Batch
            ,      D.IBS_BATCH_ITEM AS IBS_Item
            ,      D.IBS_BALANCE_DUE AS Balance_Due
            
            ,RANK()OVER(PARTITION BY  D.IBS_DEBT_TYPE_CODE ORDER BY D.IBS_DEBT_DATE ASC) as DebtPayID
            
                        
      FROM OMS_OWNER.TB_IBS_DEBT D
            INNER JOIN OMS_OWNER.TB_DEBT_TYPE DT
                  ON ( DT.IBS_DEBT_TYPE_CODE = D.IBS_DEBT_TYPE_CODE )
                  
      WHERE D.IBS_DOC_NUMBER = @DOC_NB
)            

UNION ALL

(      SELECT
                  DP.IBS_DOC_NUMBER AS DOC_NB
            ,      DP.IBS_REPAY_TYPE_CODE AS IBS_Type
            ,      RT.REPAY_TYPE_DESCRIPTION AS IBS_Description
            ,      DP.IBS_REPAY_DATE AS IBS_Date
            ,      DP.IBS_REPAY_LOC AS IBS_LOC
            ,      DP.IBS_REPAY_REFERENCE AS IBS_REF
            ,      DP.IBS_REPAY_AMOUNT AS Amount
            ,      DP.IBS_DEBT_TYPE_CODE AS Debt_Code
            ,      DP.IBS_DEBT_DATE AS Debt_Date
            ,      NULL
            
            ,RANK()OVER(PARTITION BY DP.IBS_DEBT_TYPE_CODE  ORDER BY DP.IBS_DEBT_DATE ASC) AS DebtPayID
                              
      FROM OMS_OWNER.TB_IBS_DEBT_PAY DP
            INNER JOIN OMS_OWNER.TB_REPAY_TYPE RT
            ON ( RT.IBS_REPAY_TYPE_CODE = DP.IBS_REPAY_TYPE_CODE )
            LEFT OUTER JOIN #TEMP_DEBT D ON ( D.IBS_Type =
                                          P.IBS_DEBT_TYPE_CODE
            AND D.IBS_Date = DP.IBS_DEBT_DATE
            AND D.IBS_REF = DP.IBS_DEBT_REFERENCE )
            
      WHERE DP.IBS_DOC_NUMBER = @DOC_NB
            
);
0
 
PortletPaulfreelancerCommented:
Yes, I believe you gave us the existing code for the union - but there is nothing "in both parts of the temp table" that matches a payment to debt (except the doc_nb) but this will always the the same value in all row due the selection being used.

In other words "there is something missing" to achieve the desired sort order. Recall the spreadsheet, where we can manually move 3 rows to under a payment - but there is no piece of information in the temp table to arrive at that sort - somewhere in your head you have this small key to success - what makes you recognize that those 3 rows need moving....

are there any fields that allow you to do this join? (debt to payment)

FROM TB_IBS_DEBT D
LEFT OUTER JOIN TB_IBS_DEBT_PAY DP ON D.<<what ??>> = DP.<<what ??>>
                                                                         AND << all of them please >>
0
 
SherryDeveloperAuthor Commented:
I have this in the temp table, 2nd table of the union all join

LEFT OUTER JOIN #TEMP_DEBT D ON ( D.IBS_Type = DP.IBS_DEBT_TYPE_CODE
                        AND D.IBS_Date = DP.IBS_DEBT_DATE
                        AND D.IBS_REF = DP.IBS_DEBT_REFERENCE )


Below is the query to create the temp table, using a union all between the debt and payment tables.

--=======================================================================================
CREATE TABLE #TEMP_DEBT
(  DOC_NB NUMERIC(10,0), IBS_Type CHAR(2),IBS_Description VARCHAR(20),IBS_Date DATE,
            IBS_LOC CHAR(3), IBS_REF CHAR(6), Amount NUMERIC(10,2), IBS_Batch CHAR(3),
            IBS_Item CHAR(4),Balance_Due NUMERIC(10,2),DebtPayID INT
)

INSERT INTO #TEMP_DEBT
(  DOC_NB, IBS_Type,IBS_Description,IBS_Date,IBS_LOC,IBS_REF,Amount,IBS_Batch
            ,IBS_Item,Balance_Due,DebtPayID
)
      
(      SELECT
                  D.IBS_DOC_NUMBER AS DOC_NB
            ,      D.IBS_DEBT_TYPE_CODE AS IBS_Type
            ,      DT.DEBT_TYPE_DESCRIPTION  AS IBS_Description
            ,      D.IBS_DEBT_DATE AS IBS_Date
            ,      D.IBS_DEBT_LOCATION AS IBS_LOC
            ,      D.IBS_DEBT_REFERENCE  AS IBS_REF
            ,      D.IBS_DEBT_AMOUNT AS Amount
            ,      D.IBS_BATCH_NUMBER AS IBS_Batch
            ,      D.IBS_BATCH_ITEM AS IBS_Item
            ,      D.IBS_BALANCE_DUE AS Balance_Due
            
            ,RANK()OVER(PARTITION BY  D.IBS_DEBT_TYPE_CODE ORDER BY D.IBS_DEBT_DATE ASC) as DebtPayID
            
                        
      FROM OMS_OWNER.TB_IBS_DEBT D
            INNER JOIN OMS_OWNER.TB_DEBT_TYPE DT
                  ON ( DT.IBS_DEBT_TYPE_CODE = D.IBS_DEBT_TYPE_CODE )
                  
      WHERE D.IBS_DOC_NUMBER = @DOC_NB
)            

UNION ALL

(      SELECT
                  DP.IBS_DOC_NUMBER AS DOC_NB
            ,      DP.IBS_REPAY_TYPE_CODE AS IBS_Type
            ,      RT.REPAY_TYPE_DESCRIPTION AS IBS_Description
            ,      DP.IBS_REPAY_DATE AS IBS_Date
            ,      DP.IBS_REPAY_LOC AS IBS_LOC
            ,      DP.IBS_REPAY_REFERENCE AS IBS_REF
            ,      DP.IBS_REPAY_AMOUNT AS Amount
            ,      DP.IBS_DEBT_TYPE_CODE AS Debt_Code
            ,      DP.IBS_DEBT_DATE AS Debt_Date
            ,      NULL
            
            ,RANK()OVER(PARTITION BY DP.IBS_DEBT_TYPE_CODE  ORDER BY DP.IBS_DEBT_DATE ASC) AS DebtPayID
                              
      FROM OMS_OWNER.TB_IBS_DEBT_PAY DP
            INNER JOIN OMS_OWNER.TB_REPAY_TYPE RT
            ON ( RT.IBS_REPAY_TYPE_CODE = DP.IBS_REPAY_TYPE_CODE )
            LEFT OUTER JOIN #TEMP_DEBT D ON ( D.IBS_Type =
                                                              DP.IBS_DEBT_TYPE_CODE
                        AND D.IBS_Date = DP.IBS_DEBT_DATE
                        AND D.IBS_REF = DP.IBS_DEBT_REFERENCE )
            
      WHERE DP.IBS_DOC_NUMBER = @DOC_NB
            
);
0
 
SherryDeveloperAuthor Commented:
Got this figured out.  Used a with and joined the results of the first two queries and added a third sort item in the ranking.  Thanks for your help.
0
 
PortletPaulfreelancerCommented:
great! glad to hear its working - cheers
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now