Describes how to add an automatic number to the query result. This is often the case. I need to add a column of numbers similar to Identity in the query results. although it is not difficult to implement it in Client programming, however, sometimes I want to retain the existing Cl. I often need to add a column of numbers similar to Identity in the query results. although it is not difficult to implement it in Client programming, however, sometimes I want to retain the existing Class and do not want to perform additional coding on the Client side, so I have to find a solution in SQL.
First, we will introduce a method to complete with an SQL statement. The principle is to query the number of records greater than or equal to this record in the result to obtain its Rank.
Example:
USE pubs
Select count (*) AS Rank, a1.au _ lname, a1.au _ fname
FROM authors a1, authors a2
WHERE a1.au _ lname a1.au _ fname> = a2.au _ lname a2.au _ fname
Group by a1.au _ lname, a1.au _ fname
Order by Rank
However, this method has its own limitations. The first is poor performance, and the second is that if the same record exists, Rank will be tied. for example, if there are two 2, but no more than 3
Is there any other way? Of course, SQL provides an IDENTITY Function to obtain the value of the ID column. Unfortunately, this Function can only be used in SELECT INTO statements, so we have to introduce a temporary table.
Example:
USE pubs
Select identity (INT, 1, 1) AS Rank, au_lname, au_fname
INTO # tmp
FROM authors
SELECT * FROM # tmp
Drop table # tmp
The performance and applicability of this method are better than those of the first method, but the disadvantage is that several SQL statements must be used to complete the process.
If possible, we recommend that you complete this operation on the client.
Thanks for your read and any advise.
...
