Appian Community
Site
Search
Sign In/Register
Site
Search
User
DISCUSS
LEARN
SUCCESS
SUPPORT
Documentation
AppMarket
More
Cancel
I'm looking for ...
State
Suggested Answer
+1
person also asked this
people also asked this
Replies
9 replies
Answers
1 answer
Subscribers
9 subscribers
Views
33062 views
Users
0 members are here
Share
More
Cancel
Related Discussions
Home
»
Discussions
»
General
Generate column with unique ID in MySQL view
John Stretton
over 7 years ago
I've often found the need to create views which do not have a single-column primary key, but we must have this primary key column in order to publish a data store which connects to such a view. I suspect that other practitioners have encountered the same issue, so I thought I'd write a quick note about the solutions that I have found. Perhaps some of this information could be included in a KB article.
In SQL Server and Oracle, the ROW_NUMBER() function meets our needs. Your view definition might look something like this:
SELECT ISNULL(CAST(ROW_NUMBER() OVER (ORDER BY your_ordering_column_name) AS int), 0) AS id, [other column names]
FROM your_tables
However, in MySQL we have to be more creative. There is a way to generate row numbers by making use of a variable inside the query:
SELECT t.*, @rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t, (SELECT @rownum := 0) r
However, if you try to cre...
OriginalPostID-272952
Discussion posts and replies are publicly visible
Top Replies
John Stretton
over 7 years ago
+2
suggested
...ate a view using this method, you'll get an error message telling you that variables are not allowed in view definitions. To get around this restriction, we can create a function that initializes a…
John Stretton
over 7 years ago
+1
...a data store that connects to the view. Credit for this solution goes to the users who posted on this stackoverflow entry: stackoverflow.com/.../create-a-view-with-column-num-rows-mysql