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
Parents
0
aloks0189
Certified Lead Developer
over 7 years ago
@christineh i tried that, initially it was working properly because of having unique PKs combination, but after having huge number of rows getting return from View, i got the duplicate PK again (which was rare, but got that) and again i came back into the same situation where i was earlier. So i tried the solution mention by @Johns but while creating the view it was giving Syntax error but while executing the same in Normal Query was working fine and returning sequence number for each row.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
Reply
0
aloks0189
Certified Lead Developer
over 7 years ago
@christineh i tried that, initially it was working properly because of having unique PKs combination, but after having huge number of rows getting return from View, i got the duplicate PK again (which was rare, but got that) and again i came back into the same situation where i was earlier. So i tried the solution mention by @Johns but while creating the view it was giving Syntax error but while executing the same in Normal Query was working fine and returning sequence number for each row.
Cancel
Vote Up
0
Vote Down
Sign in to reply
Verify Answer
Cancel
Children
No Data