|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
July 2nd, 2009, 06:07 AM
|
||||
|
||||
|
Assigning unique number to each query result
Hi Guys,
I have a SQLServer 2000 Database which holds details of mobile devices within our company. One table stores comments associated with the devices: tbl_comments: identifier numeric Identity device_id numeric comment_detail varchar 200 comment_by char(6) comment_date date Sample Data: identifier device_id comment_detail comment_by comment_date 1 126 comment1 Rob 01/06/2009 2 130 comment1 Dave 03/06/2009 3 126 comment2 Jane 20/06/2009 4 126 comment3 Rob 01/07/2009 5 126 comment4 Steve 02/07/2009 In this example there are 5 comments, 4 of which relate to device_id 126 and 1 for device_id 130. I would like to display all comments for device_id 126 in reverse order of comment_date - no problems there: Code:
SELECT identifier, device_id, comment_detail, comment_by, comment_date FROM tbl_comments WHERE device_id = 126 ORDER BY comment_date DESC Result: identifier device_id comment_detail comment_by comment_date 5 126 comment4 Steve 02/07/2009 4 126 comment3 Rob 01/07/2009 3 126 comment2 Jane 20/06/2009 1 126 comment1 Rob 01/06/2009 I would now like to number each row in reverse order, so that the first row is the total number of rows, counting down to 1 which is the oldest comment. eg: Result Number identifier device_id comment_detail comment_by comment_date 4 5 126 comment4 Steve 02/07/2009 3 4 126 comment3 Rob 01/07/2009 2 3 126 comment2 Jane 20/06/2009 1 1 126 comment1 Rob 01/06/2009 I am developing a web application using Classic ASP so I know that I could execute a sql statement to count the number of rows which match the criteria and then set a variable equal to this value and decrement it by one each time I display a row, but I was wondering if there was a way to do this within the sql statement itself. I hope this makes sense, any ideas appreciated.
|
|
#2
July 2nd, 2009, 06:33 AM
|
||||
|
||||
|
Resolved.......... But welcome any better ways of doing it!!
Managed to find a way of doing this by counting the number of records which have an identifier less than the identifier of the current row.
Code:
SELECT c1.identifier, c1.device_id, c1.comment_detail, c1.comment_by, c1.comment_date, (SELECT COUNT(*) FROM tbl_comments c2 WHERE c2.identifier <= c1.identifier AND c2.device_id = 126) AS RowNumber FROM tbl_comments c1 WHERE c1.device_id = 126 ORDER BY c1.comment_date DESC This works great for my needs because the identifier is a unique numeric value, but I dont know how this would work if the column is a string/non-unique value. If anyone has any comments or knows a better way of doing this I would appreciate your comments. Last edited by sync_or_swim : July 2nd, 2009 at 07:51 AM.
|
|
#3
July 9th, 2009, 02:32 PM
|
||||
|
||||
|
It depends... if you are using SQL Server 2005 you can use RANK() for this. Here's a very simplified example:
Code:
SELECT TOP 10 *, RANK() OVER(ORDER BY some_PK) FROM SomeTable I imagine the order by could be the comment ID descending since the newest comments would have the higher unique number. However, I do like your method. It's quite creative!
__________________
Slarentice (origin:Shadow Wizard of ASP Free) [noun] A slave and apprentice of the Wizard's Circle (specifically of mehere) at ASP Free. ---- If shemzilla takes over, it's best to be on his good side  
|
|
#4
July 10th, 2009, 04:30 AM
|
||||
|
||||
|
Laura,
Thank you very much for the suggestion. I am using SQLServer 2000 in this project so am unable to use RANK() but I have tested this in 2005 and it works great!! I'm sure this will come in handy in the future!! Thanks again for the comment, if anything else has any other suggestions I'd be glad to hear them.
|
|
#5
October 13th, 2009, 12:38 AM
|
|||
|
|||
|
Re: Assign unique number to each query result
Hi,
I hope that the below SQL query will be very useful for you Code:
Select row_number()over(order by identifier asc) as Result Number, identifier,device_id,comment_detail,comment_by, convert(char,comment_date,101)as comment_date from tbl_comments where device_id = 126 order by identifier desc Thanks & Regards Sakthimeenakshi.S ------------------------------------------------------------
|
|
| Viewing: ASP Free Forums > Database > SQL Development > Assigning unique number to each query result |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
