Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ROWNUM as a pseudo column equivalent in T-SQL?

When using ROWNUM as a pseudo column how would you write the following query using T-SQL? Here is the full query. I realize it's written in PL/SQL and I will need to change addition objects.

BASE AS (
SELECT NULL AS ES_CMPNY_STATUS_KEY
  ,CSG.CMPNY_STATUS_GROUP
  ,CSG.CMPNY_STATUS_GROUP_ID
  ,CSG.CMPNY_STATUS_REASON
  ,CSG.CMPNY_STATUS_REASON_ID
  ,CSF.CMPNY_CURRENT_STATUS_FLAG
  ,TRUNC(SYSDATE) AS LOAD_DATE
  FROM CMPNY_STATUS_GROUP CSG
  CROSS JOIN CMPNY_CURRENT_STATUS_FLAG CSF
  ORDER BY CSG.CMPNY_STATUS_GROUP, CSG.CMPNY_STATUS_REASON

   )SELECT ROWNUM AS ES_CMPNY_STATUS_KEY
   ,CMPNY_STATUS_GROUP
   ,CMPNY_STATUS_GROUP_ID
   ,CMPNY_STATUS_REASON
   ,CMPNY_STATUS_REASON_ID
   ,CMPNY_CURRENT_STATUS_FLAG
   ,LOAD_DATE
 FROM BASE B
UNION
SELECT 0 AS ES_CMPNY_STATUS_KEY
  ,NULL AS CMPNY_STATUS_GROUP
  ,0 AS CMPNY_STATUS_GROUP_ID
  ,NULL AS CMPNY_STATUS_REASON
  ,0 AS CMPNY_STATUS_REASON_ID
  ,0 AS CMPNY_CURRENT_STATUS_FLAG
  ,TRUNC(SYSDATE) AS LOAD_DATE
  FROM DUAL
like image 319
PJ.SQL Avatar asked Jun 07 '18 20:06

PJ.SQL


People also ask

Which are pseudo columns Rownum?

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

What is the difference between rownum pseudo column and ROW_NUMBER () function?

What is the Difference between ROWNUM And ROW_NUMBER()? ROWNUM is a "Pseudocolumn" that assigns a number to each row returned by a query. ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows.

What is ROW_NUMBER () over ORDER BY column?

The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.

Is Rownum an actual column in a table?

ROWID & ROWNUM are pseudocolumns which are not actual columns in the table but behave like actual columns. You can select the values from pseudocolumns like ROWID & ROWNUM. ROWID & ROWNUM are very important pseudocolumns in oracle which is used in data retrieval.


1 Answers

Use row_number():

select row_number() over (order by (select null)) as ES_CMPNY_STATUS_KEY

Note that the order by is needed. The (select null) appears -- in practice -- to avoid any additional sorting. In general, though, you would include a column that specifies a sort order for the data you want.

like image 69
Gordon Linoff Avatar answered Sep 27 '22 23:09

Gordon Linoff