PARTITION BY 1 vs PARTITION BY COLUMN NAME

Database
Enthusiast

PARTITION BY 1 vs PARTITION BY COLUMN NAME

Data set:

CREATE VOLATILE TABLE TAB1
(
ID INTEGER
, NM VARCHAR (10)
) PRIMARY INDEX (ID)
ON COMMIT PRESERVE ROWS;

INSERT INTO TAB1 VALUES (558, 'NAME 1');
INSERT INTO TAB1 VALUES (558, 'NAME 2');
INSERT INTO TAB1 VALUES (559, 'NAME 3');
INSERT INTO TAB1 VALUES (559, 'NAME 2');

Query 1:

SELECT
ID
, NM
, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1)
FROM TAB1;

Output 1:

image.png

Query 2:

SELECT
ID
, NM
, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)
FROM TAB1;

Output 2:

image.png

What is the difference between PARTITION BY 1 vs PARTITION BY ID (actual column name) here? Typically, 1 is referred to the first column. However, it is not like that here. Can't seem to understand why. Can someone please explain or point me to the right document that can explain this difference as to why it is showing 2 different datasets?

This may be completely basic, but I cannot seem to find the reason anywhere.

 

Thanks in advance!

 

-Som

SR

Accepted Solutions
Highlighted
Teradata Employee

Re: PARTITION BY 1 vs PARTITION BY COLUMN NAME


@Somdebroy wrote:

What is the difference between PARTITION BY 1 vs PARTITION BY ID (actual column name) here? Typically, 1 is referred to the first column.


Hi Som, you're wrong here.

 

This is only true in final ORDER BY of a query (not ORDER BY inside analytic function), and in some RDBMS, including Teradata Database, also for the GROUP BY clause but this one is not ANSI standard.

Partition by 1 will add the 1 constant to every rows of your dataset then partition by it, similar to omitting the PARTITION BY clause.

1 ACCEPTED SOLUTION
2 REPLIES 2
Highlighted
Teradata Employee

Re: PARTITION BY 1 vs PARTITION BY COLUMN NAME


@Somdebroy wrote:

What is the difference between PARTITION BY 1 vs PARTITION BY ID (actual column name) here? Typically, 1 is referred to the first column.


Hi Som, you're wrong here.

 

This is only true in final ORDER BY of a query (not ORDER BY inside analytic function), and in some RDBMS, including Teradata Database, also for the GROUP BY clause but this one is not ANSI standard.

Partition by 1 will add the 1 constant to every rows of your dataset then partition by it, similar to omitting the PARTITION BY clause.

Enthusiast

Re: PARTITION BY 1 vs PARTITION BY COLUMN NAME

Thank you Walder! It makes sense.

SR