Min not working for ROW_NUMBER

• Thom A

  SSC Guru

  Points: 99143

  More actions

  November 3, 2017 at 8:06 am

  #1966563

  Without the original data, how can we hope to get the results your after? Could you please post DDL and consumable sample data?

  Thanks.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  November 3, 2017 at 8:50 am

  #1966569

  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

• nicolestrain07

  SSC Journeyman

  Points: 84

  More actions

  November 3, 2017 at 9:02 am

  #1966573

  drew.allen - Friday, November 3, 2017 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

  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

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  November 3, 2017 at 9:36 am

  #1966576

  nicolestrain07 - Friday, November 3, 2017 9:02 AM

  drew.allen - Friday, November 3, 2017 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

  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

• nicolestrain07

  SSC Journeyman

  Points: 84

  More actions

  November 3, 2017 at 10:22 am

  #1966578

  drew.allen - Friday, November 3, 2017 9:36 AM

  nicolestrain07 - Friday, November 3, 2017 9:02 AM

  drew.allen - Friday, November 3, 2017 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

  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

  I added a partition to my Row_Number and it worked ... Thank you