Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-06-09 : 08:23:38
|
| Andy writes "I have a rather complicated problem which I have searched widely for an answer, unfortunately without any result.I have created a view which draws from one table. It draws the following values, "convoypos"(numeric) and "thumb"(an image url) and order(s) by convoypos asc. Simple, nice set of results.What I would like to be able to do is 'add' another column to this view, the value of which is an auto generated number.So that I would then have 3 columns "scrollpos", "convoypos" and "thumb".The reason for this is, to be able to assign a temporary numerical identity to the results of "scrollpos", in the view, for a rather complicated Flash action script.When the view is called, the other 2 columns would then be ordered by the new additional column.Does this sound complicated or what? Or am I going about it in completely the wrong way?I do hope that you find this a challenging question, enough to grant me a reply.I look forward to hearing from you if you can help me.Best regardsAndysql 2000 + win 2000" |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-09 : 11:00:45
|
| How about:SELECT NEWID()Brett8-) |
 |
|
|
dsdeming
479 Posts |
Posted - 2003-06-09 : 13:47:40
|
quote:
When the view is called, the other 2 columns would then be ordered by the new additional column.
Wouldn't using NEWID() effectively randomize the order of the rows? It sounds like Andy is looking for something like IDENTITY instead. At least that will return the same set each time, as long as the WHERE and ORDER BY clauses aren't changed.Dennis |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-09 : 13:52:05
|
| OK, then I have to ask, how do you arbitrarily assign a number for order purposes to a row?What rules would you use to determin which row should be seeded 1st, 2nd, 3rd, ect?Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-09 : 14:54:06
|
| You can do this in a stored procedure with the IDENTITY() function.CREATE PROCEDURE AddNumbering()ASselect a.*, identity(int,1,1) as IDinto #tempTablefromYourTable aselect * from #temptableThat's one way of doing it .... there are others. None are too elegant.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-09 : 15:43:03
|
quote:
That's one way of doing it .... there are others. None are too
Nor is there an elegant reason to arbitrarily assign order to chaos....Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-09 : 16:36:22
|
| If you have an order by in the view you must also have a top 100 percent for it to work I guess.tryselect top 100 percentconvoypos ,thumb ,scrollpos = (select count(*) from tbl t2 where t2.convoypos <= tbl.convoypos)from tblorder by scrollposThis will give duplicate scrollpos for and duplicate convoypos. You could get round this with a new_id() probably.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-06-09 : 17:24:04
|
| Brett -- read his post. He mentions that the results should be ordered by what sounds like the primary key of the table.It is fun to tell people how moronic they are for not understanding that SQL is set-based and and all of that fun stuff, but there ARE concepts of row numbers and paging and physical ordering whether the theorists like them or not because we live in a universe that requires all of that.We've all used TOP N queries and SET ROWCOUNT before .... same deal. By definition, NEITHER of those can possibly work unless SQL Server is *somehow* keeping track of which row is returned first, then which one is next, and the next, etc -- in other words, numbering the rows!- Jeff |
|
|
|
|
|
|
|
|
adding another column to a view