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
ROW_NUMBER() OVER (PARTITION...
Posted - 11/20/2012 : 08:55:32
