| Author |
Topic |
|
ann06
Posting Yak Master
171 Posts |
 Posted - 2008-09-04 : 03:36:59
|
| Hi All,i have a view i want to show an additional column in the view that it's not exist in a table. can i add a column that is the serial incremental column that shows the number for each row in the result of the view?Thanks in advance :*) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 03:39:09
|
| yup you can. are you using sql 2005? if yes, you can use ROW_NUMBER() function for this purpose. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-04 : 03:41:05
|
if you are using SQL server 2005, you can use the row_number() function
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-04 : 03:41:32
|
 too late.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 03:50:28
|
| now am using 2000 :S !!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 03:58:17
|
quote:
Originally posted by ann06
now am using 2000 :S !!!!
what order you want to number the records. based on what field?also will it be unique valued? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-04 : 03:59:52
|
Why do you need the record no in the first place ? Do you need a unique identification for you records ?why not create an identity column in the table ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 04:08:05
|
| the view is a filter on the table so adding an identity column will not get the serial increment in the result of the view because iam using group by in the viewi need like thisindex clolumn1---------------1 x2 y3 z4 f5 asdasdfasd:D |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 04:10:50
|
| but you need a column based on which you want to serialise the index. what should be the column used?also specify whether it will contain duplicate values |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-04 : 04:11:55
|
You have not explain why do you need the serial no there ? These can be easily done at your front end application where you are displaying the data
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-09-04 : 05:10:14
|
quote:
Originally posted by ann06
Hi All,i have a view i want to show an additional column in the view that it's not exist in a table. can i add a column that is the serial incremental column that shows the number for each row in the result of the view?Thanks in advance :*)
Where do you want to show data?MadhivananFailing to plan is Planning to fail |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 05:37:27
|
| i want it to display the row number only, we have an application the reports are generated using a view,there is no option in the application to add this column so it must be done in the database in order to show cuz it only show what is taken from the database sorry for being late to reply |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 05:40:34
|
quote:
Originally posted by ann06
i want it to display the row number only, we have an application the reports are generated using a view,there is no option in the application to add this column so it must be done in the database in order to show cuz it only show what is taken from the database sorry for being late to reply
which platform is this application based on? |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 06:07:50
|
| asp.net i dont know asp and there is no customization on this application |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 06:38:55
|
quote:
Originally posted by ann06
asp.net i dont know asp and there is no customization on this application
ok in that case can you specify unique column based on which you need the numbering as requested earler? |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 06:46:37
|
| if i specified a unique column in the database it won't help because iam using group by function in the view and in this case the serial column can not be shown in the view, what i want is for each row generated in the view there should be like row no. column so that i can do sorting or can know the total count |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-09-04 : 06:49:19
|
can you use stored procedure ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-04 : 06:57:09
|
quote:
Originally posted by ann06
if i specified a unique column in the database it won't help because iam using group by function in the view and in this case the serial column can not be shown in the view, what i want is for each row generated in the view there should be like row no. column so that i can do sorting or can know the total count
nope. i was asking if there was already any column in your db with unique values. (i couldnt find one in structure posted) |
|
|
|
ann06
Posting Yak Master
171 Posts |
Posted - 2008-09-04 : 07:18:02
|
| no only views i can read from. i guess its not possible :(i will try to contact the developers to have support methanks guys for helping out, your support is much appreciated best site ever :D |
|
|
|
|
Serial column in a view?