CODEWITHSUNDEEP
sqlamazon-athenapresto
Roméo Després
Roméo Després

Reputation: 2183

Unique row identifier in Presto SQL

I work on Presto SQL tables that don't have unique row identifiers. The only way to identify a specific record is to query all of its fields.

Is there in Presto some kind of hidden field, say ROW_PRIMARY_KEY, that would allow me to uniquely identify records in my tables?

Upvotes: 4

Views: 8075

Answers (2)

Asclepius
Asclepius

Reputation: 63484

To extend and simplify the answer by JNevill, if you just want a row number:

SELECT row_number() OVER () AS row_num

Note that OVER () may function the same as OVER (PARTITION BY 1), implying that all rows are assigned to the same partition. In this way, all rows will have unique row numbers.

Upvotes: 7

JNevill
JNevill

Reputation: 50218

Short of a primary key, you could just toss in a 

ROW_NUMBER() OVER (PARTITION BY some, columns ORDER BY some_other_column) as rn

This will define a row number where some, columns would be a psuedo-primary key.

Upvotes: 4

Related Questions