Need a Column to Identify the Row Number

Need a Column to Identify the Row Number

Post by Duane Hause » Fri, 27 Jul 2001 12:56:16



I need to show the row number that is being displayed in a query.  I am going to reorganize the data by Last Name when I select it and I want to associate the proper Alpha number with it.

I would prefer NOT to use the Identity column.

Duane Hauser

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Sudhi » Fri, 27 Jul 2001 13:22:22


Try using a trigger on insert.  So when you insert a new
row, the trigger will get fired and will insert a value
in the row id column. Create a table to hold the last
value, so the trigger will know the next value to
insert.  
Quote:>-----Original Message-----
>I need to show the row number that is being displayed in

a query.  I am going to reorganize the data by Last Name
when I select it and I want to associate the proper Alpha
number with it.
Quote:

>I would prefer NOT to use the Identity column.

>Duane Hauser

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.


 
 
 

Need a Column to Identify the Row Number

Post by Duane Hause » Fri, 27 Jul 2001 13:35:22


I am just wanting to do a select on a table and create the row counter at that time.

Duane Hauser

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Tony Rogerso » Fri, 27 Jul 2001 15:22:42


Is there a reason you do not want to use identities ?

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials etc...]


Quote:> I need to show the row number that is being displayed in a query.  I am

going to reorganize the data by Last Name when I select it and I want to
associate the proper Alpha number with it.
Quote:

> I would prefer NOT to use the Identity column.

> Duane Hauser

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Tome » Fri, 27 Jul 2001 18:58:52


Hi,
You can use table variable, that one of his columns his an identity, And use

Select XYZ From TableName".
And you'll get your enumerator.


Quote:> I need to show the row number that is being displayed in a query.  I am

going to reorganize the data by Last Name when I select it and I want to
associate the proper Alpha number with it.
Quote:

> I would prefer NOT to use the Identity column.

> Duane Hauser

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Marc A. Lewandowsk » Sat, 28 Jul 2001 02:56:45


If I am understanding you correctly, you don't mind that these 'row numbers'
will change every time something gets inserted into the list?  That is, are
you asking for a row ranking by Name order?

So, if your table contains:

LastName
----------------
Ammon
Hauser
Lewandowski
Johnson
Thompson

you want the numbers to appear:

1    Ammon
2    Hauser
4    Lewandowski
3    Johnson
5    Thompson

but if you insert 'Bates' you'll get

1    Ammon
3    Hauser
5    Lewandowski
4    Johnson
6    Thompson
2    Bates

If this is the behavior you want, try this (got no DDL, so working from
scratch):

select t.LastName, Rank = count(*)
from Table t
    inner join Table t1 on (t1.LastName <= t.LastName)
group by t.LastName

-marc


Quote:> I need to show the row number that is being displayed in a query.  I am

going to reorganize the data by Last Name when I select it and I want to
associate the proper Alpha number with it.
Quote:

> I would prefer NOT to use the Identity column.

> Duane Hauser

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by dragon zhan » Sat, 28 Jul 2001 03:47:17


I just wonder what would happen about the count(*)
if there are ties?

dragon

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Joe Celk » Sat, 28 Jul 2001 04:35:33


Duplicte rows would mean that you did not have a key.  If you did not have a key, then youdid not have a table in the first place.  If you wanted to use this row to fake a PHYSICAL row ordering, then you are not writing SQL, which is a set-oriented language.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Duane Hause » Sat, 28 Jul 2001 05:11:41


I do not want to do this on a temporary table.

Duane Hauser

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Vaughan Powel » Sat, 28 Jul 2001 05:32:55


Duane,

Here's a variation on the above which will force the order of the data in the table variable and guarantee the correct row numbering in SQL Server 2000 - sorry Joe Celko - I know it's not true SQL and not ANSII standard but it works really well for paging search results on web sites.  Specific row number ranges can be retrieved for web search pages results without the use of inefficient and complex sub queries. You pays your money and you takes your choice...

In SQL 2K  set up a table variable with an identity column (call it RowNo) and a LastName column and then:

INSERT

(
            LastName
)
SELECT      TOP 100 PERCENT
            LastName
FROM        TableName
ORDER BY    LastName

SELECT      *

ORDER BY    RowNo

In SQL Server 7 the same functionality can be obtained using a temporary table and the IDENTITY function:

SELECT      RowNo = IDENTITY (int,1,1),
            LastName

INTO       #OrderedTable

FROM
(
SELECT      TOP 100 PERCENT
            LastName
FROM        TableName
ORDER BY    LastName
) AS OrderedTable

SELECT      *
FROM       #OrderedTable
ORDER BY    RowNo

Note that the derived query (in brackets) is essential as otherwise the IDENTITY values would be assigned before the ORDER BY is performed and would be given the wrong values.

Regards

Vaughan Powell
MCDBA, MCSD, MCSE
Internet Database Architect

Keybright Services Ltd
<http://www.keybright.co.uk/>

'Databases for the Internet'

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by dragon zhan » Sat, 28 Jul 2001 06:08:56


your assumption is not right.
obviously you could have a table and
it has no key!

sometime you have to work on some shEEt.

of course Normalized ones will make
my life easier.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Need a Column to Identify the Row Number

Post by Joe Celk » Sat, 28 Jul 2001 07:41:24


Quote:>> obviously you could have a table and it has no key! <<

No, I can do a CREATE TABLE and get something that I can use as a work area, but it is not a table (i.e. a logically correct model of a set of entities or relationships).

Quote:>> of course Normalized ones will make my life easier. <<

That is my point; why add columns that have nothing to do with the LOGICAL data model and only relate to the CURRENT PHYSICAL storage?  Why do extra work to make your life hard?  That is why we have managers and clients ...

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!