November 3, 2017 at 8:05 am
Hello , I dont think what im trying to do is possible but could someone advice on a correct way of getting the result based on the examples below ?
Im trying to get the MIN of the ROW_Number however it just seems to by pass the statement without throwing any errors strangely .
;with CTE
as
(SELECT
ID,
ROW_NUMBER () OVER (ORDER BY Orders.StockCode) tempID,
Orders.EnquiryID,
Orders.StockCode,
Stock.Available,
COUNT (Orders.StockCode) OVER (PARTITION BY Orders.StockCode) AS TotalOrders
FROM
Orders
INNER JOIN Stock on Orders.StockCode = Stock.StockCode
)
Select * from CTE
Where CTE.tempID > (SELECT MIN(CTE.tempID) from CTE) + Available -1
The result im getting is
| ID | tempID | EnquiryID | StockCode | Available | TotalOrders |
| 34 | 6 | 123 | 1 | 3 | 6 |
| 39 | 7 | 124 | 1 | 3 | 6 |
| 40 | 8 | 125 | 1 | 3 | 6 |
| 45 | 9 | 126 | 1 | 3 | 6 |
| 52 | 10 | 127 | 1 | 3 | 6 |
| 50 | 11 | 128 | 1 | 3 | 6 |
however i am expecting this result
| ID | tempID | EnquiryID | StockCode | Available | TotalOrders |
| 45 | 9 | 126 | 1 | 3 | 6 |
| 52 | 10 | 127 | 1 | 3 | 6 |
| 50 | 11 | 128 | 1 | 3 | 6 |
Thanks in advance
November 3, 2017 at 8:50 am
I think the error is in your logic, not in the functionality. I'm not sure what you're expecting the MIN of the ROW_NUMBER to produce, but it will always be 1.
Select * from CTE
Where CTE.tempID > (SELECT MIN(CTE.tempID) from CTE) + Available -1
/** MIN(CTE.TempID) = 1 therefore this reduces to **/
Select * from CTE
Where CTE.tempID > (SELECT 1 from CTE) + Available -1
/** Which further reduces to **/
Select * from CTE
Where CTE.tempID > 1 + Available -1
/** And finally reduces to **/
Select * from CTE
Where CTE.tempID > Available
Since all of the results returned seem to match that pattern, it looks like this is working perfectly.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2017 at 9:02 am
drew.allen - Friday, November 3, 2017 8:50 AMI think the error is in your logic, not in the functionality. I'm not sure what you're expecting the MIN of the ROW_NUMBER to produce, but it will always be 1.
Select * from CTE
Where CTE.tempID > (SELECT MIN(CTE.tempID) from CTE) + Available -1/** MIN(CTE.TempID) = 1 therefore this reduces to **/
Select * from CTE
Where CTE.tempID > (SELECT 1 from CTE) + Available -1/** Which further reduces to **/
Select * from CTE
Where CTE.tempID > 1 + Available -1/** And finally reduces to **/
Select * from CTE
Where CTE.tempID > AvailableSince all of the results returned seem to match that pattern, it looks like this is working perfectly.
Drew
I though it would select the minimum row that meets my conditions .. so in my example the ROW_NUMBER is 6.
Thanks for your help .. just need to re-think the logic
November 3, 2017 at 9:36 am
nicolestrain07 - Friday, November 3, 2017 9:02 AMdrew.allen - Friday, November 3, 2017 8:50 AMI think the error is in your logic, not in the functionality. I'm not sure what you're expecting the MIN of the ROW_NUMBER to produce, but it will always be 1.
Select * from CTE
Where CTE.tempID > (SELECT MIN(CTE.tempID) from CTE) + Available -1/** MIN(CTE.TempID) = 1 therefore this reduces to **/
Select * from CTE
Where CTE.tempID > (SELECT 1 from CTE) + Available -1/** Which further reduces to **/
Select * from CTE
Where CTE.tempID > 1 + Available -1/** And finally reduces to **/
Select * from CTE
Where CTE.tempID > AvailableSince all of the results returned seem to match that pattern, it looks like this is working perfectly.
Drew
I though it would select the minimum row that meets my conditions .. so in my example the ROW_NUMBER is 6.
Thanks for your help .. just need to re-think the logic
It would help if you told us what problem you were trying to solve. I notice that your ROW_NUMBER() definition is non-deterministic. This is potentially a big problem. I also noticed that your COUNT() uses a partition, but your ROW_NUMBER() does not. Typically, you want to use the same partition for both, but there are exceptions. You haven't given us enough detail to determine whether this is one of those exceptions, but my inclination is that it's not.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2017 at 10:22 am
drew.allen - Friday, November 3, 2017 9:36 AMnicolestrain07 - Friday, November 3, 2017 9:02 AMdrew.allen - Friday, November 3, 2017 8:50 AMI think the error is in your logic, not in the functionality. I'm not sure what you're expecting the MIN of the ROW_NUMBER to produce, but it will always be 1.
Select * from CTE
Where CTE.tempID > (SELECT MIN(CTE.tempID) from CTE) + Available -1/** MIN(CTE.TempID) = 1 therefore this reduces to **/
Select * from CTE
Where CTE.tempID > (SELECT 1 from CTE) + Available -1/** Which further reduces to **/
Select * from CTE
Where CTE.tempID > 1 + Available -1/** And finally reduces to **/
Select * from CTE
Where CTE.tempID > AvailableSince all of the results returned seem to match that pattern, it looks like this is working perfectly.
Drew
I though it would select the minimum row that meets my conditions .. so in my example the ROW_NUMBER is 6.
Thanks for your help .. just need to re-think the logicIt would help if you told us what problem you were trying to solve. I notice that your ROW_NUMBER() definition is non-deterministic. This is potentially a big problem. I also noticed that your COUNT() uses a partition, but your ROW_NUMBER() does not. Typically, you want to use the same partition for both, but there are exceptions. You haven't given us enough detail to determine whether this is one of those exceptions, but my inclination is that it's not.
Drew
I added a partition to my Row_Number and it worked ... Thank you
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply