CODEWITHSUNDEEP
sqlteradata
Patrick S
Patrick S

Reputation: 3

teradata row_number() over(partition by X , Y order by Z desc) = 1

I often use this in Teradata SQL assistant:

row_number() over(partition by X||Y order by Z desc) = 1

My colleague uses the same BUT with the difference that he doesn't enter "||" between two attributes, he just enters a ",". I tested some small data with both and could not find any difference in the output.

My assumption was that using "||" is kind of a concatenation of the two attributes (X and Y). Is this true or not?

Does somebody now what the difference is between "||" and "," in this command?

Many thanks in advance..

Upvotes: 0

Views: 4374

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The two are very similar. However, the version with the commas is correct (for most purposes). Consider what happens in these cases:

X    Y
A    BC
AB   C

The concatenation version will combine them both into "ABC", but you probably don't want to do that. The comma version treats the values as ('A', 'BC') and ('AB', 'C'), so they are separate -- just as if you did group by X, Y.

Only concatenate the values together if that is really your intention.

Upvotes: 5

Related Questions