Solved

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

Posted on 2013-02-06
17
387 Views
Last Modified: 2013-02-19
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
Comment
Question by:Sherry
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38860843
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
 

Author Comment

by:Sherry
ID: 38860917
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38861046
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
 

Author Comment

by:Sherry
ID: 38861066
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38862003
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
 

Author Comment

by:Sherry
ID: 38862027
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38862067
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
 

Author Comment

by:Sherry
ID: 38862088
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 38862097
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
 
LVL 18

Expert Comment

by:deighton
ID: 38863114
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
 

Author Comment

by:Sherry
ID: 38865226
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38867037
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
 

Author Comment

by:Sherry
ID: 38886283
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 38886590
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
 

Author Comment

by:Sherry
ID: 38886614
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
 

Author Comment

by:Sherry
ID: 38906501
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38907118
great! glad to hear its working - cheers
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL 2016 from Database to Datawarehouse 6 37
Group by and order by clause 28 36
SQL Help - 12 40
Row-Level Security 2 19
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now