SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 ROW_NUMBER() OVER (PARTITION...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

97 Posts

Posted - 11/20/2012 :  08:55:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:00:21  Show Profile  Reply with Quote
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
Yak Posting Veteran

97 Posts

Posted - 11/20/2012 :  09:06:33  Show Profile  Reply with Quote
Thanks for the quick and correct answer.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/20/2012 :  09:19:09  Show Profile  Reply with Quote
You are quite welcome.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/29/2012 :  05:32:10  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000