Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ROW_NUMBER() OVER (PARTITION...

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-20 : 08:55:32
The following SQL returns 2 rows as it should. However, LASTINST has a value of 1 for *both* rows. Both rows can't be row 1 ... the latest date/time should be row 1 and the older date/time should be row 2.

What am I missing:
  SELECT
TC_TESTCYCL_ID, TC_EXEC_DATE, TC_EXEC_TIME, TC_TEST_ID, ROW_NUMBER()
OVER(PARTITION BY TC_TESTCYCL_ID ORDER BY TC_EXEC_DATE DESC,TC_EXEC_TIME DESC) AS LASTINST
FROM TESTCYCL

WHERE TC_TEST_ID = 2184 AND TC_EXEC_DATE <> '' AND TC_EXEC_TIME <> ''


.. this returns:

TC_CYCLE_ID TC_EXEC_DATE TC_EXEC_TIME TC_TEST_ID LASTINST
8008 2012-11-13 17:16:53 2184 1
8011 2012-11-08 08:41:54 2184 1

... again, both rows can't be the first row. What am I missing

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 09:00:21
ROW_NUMBER function considers each group in the PARTITION BY clause separately and counts starting at 1 for each such group. In your code, you are partitioning by TC_TESTCYCL_ID. So, for each distinct value of TC_TESTCYCL_ID, it will start counting at 1 - in your result, there are two distinct TC_TESTCYCL_ID values, 8008 and 8011, and each starts with row number = 1. If you don't want them to be partitioned that way, simply remove the "PARTITION BY TC_TESTCYCL_ID".

SELECT
TC_TESTCYCL_ID, TC_EXEC_DATE, TC_EXEC_TIME, TC_TEST_ID, ROW_NUMBER()
OVER(PARTITION BY TC_TESTCYCL_ID ORDER BY TC_EXEC_DATE DESC,TC_EXEC_TIME DESC) AS LASTINST
FROM TESTCYCL

WHERE TC_TEST_ID = 2184 AND TC_EXEC_DATE <> '' AND TC_EXEC_TIME <> ''
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-20 : 09:06:33
Thanks for the quick and correct answer.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 09:19:09
You are quite welcome.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-29 : 05:32:10
Also see what you can do with row_number() function
http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -