Assigning unique number to each query result

ASP Free Forums Sponsor:

July 2nd, 2009, 05:07 AM

sync_or_swim

Moderator

Join Date: Mar 2006

Location: South Wales

Posts: 3,632

Time spent in forums: 2 Months 3 Days 15 h 14 m 45 sec
Reputation Power: 1909

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.

July 2nd, 2009, 05:33 AM

sync_or_swim

Moderator

Join Date: Mar 2006

Location: South Wales

Posts: 3,632

Time spent in forums: 2 Months 3 Days 15 h 14 m 45 sec
Reputation Power: 1909

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 06:51 AM.

July 9th, 2009, 01:32 PM

Lauramc

SQL Slarentice

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Nov 2004

Location: In My Happy Place

Posts: 1,840

Time spent in forums: 2 Weeks 2 Days 5 h 6 m 35 sec
Reputation Power: 1266

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

July 10th, 2009, 03:30 AM

sync_or_swim

Moderator

Join Date: Mar 2006

Location: South Wales

Posts: 3,632

Time spent in forums: 2 Months 3 Days 15 h 14 m 45 sec
Reputation Power: 1909

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.

October 12th, 2009, 11:38 PM

sakthi.tnj

Registered User

Join Date: Sep 2009

Posts: 24

Time spent in forums: 8 h 45 m 13 sec
Reputation Power: 0

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

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Create the Optimal Architecture for your Critical Applications
Warburton's the largest independently owned bakery in the UK faced a number of difficult challenges in providing the most robust yet efficient IT infrastructure for their organization's success. IBM's services combined with their xSeries servers created the perfect platform for their SAP environment with sufficient flexibility, and did so in very time effective fashion.

Five Best Practices for Deploying a Successful Service-Oriented Architecture
This white paper describes the benefits you can expect with SOA, and how IBM can help take your business there.

Gartner Magic Quadrant for Application Delivery Controllers
Gartner summarizes its view on Application Delivery Controllers, evaluates strengths and weaknesses of solutions, and provides Magic Quadrant reporting for a quick comparison across all vendors. Learn from Gartner how you can benefit from an all-in-one device like Citrix NetScaler that delivers the highest levels of availability, performance and security.

Knowledge is Power
What you don't know can hurt you, and is likely costing you money and increasing your security risks during an era of scarce resources. This white paper proposes six key strategies that enterprise security managers can use to improve their network defense posture.

Rationalizing the Multi-Tool Environment
The rationalized multi-tool approach is flexible, scalable and cost effective. It provides the necessary input to the IT service management business processes. It preserves prior investments in monitoring tools, empowers technologists to select the best tools with which to do their jobs, and enhances effective response to incidents.

More Free IT White Papers!