Row numbers...

[alx]

Can anybody help me with a simple problem:

How can I add row numbers to a result of a query:

SELECT ProjID, Descr FROM projects
WHERE EmpID = 123

need results in format:
1 123 First project
2 456 Second project
3 ...

I cannot find a simple solution.
Thanks

[Keith Kratochvil]

Directly from Transact-SQL Help [the identity function]

Because this function creates a column in a table, a name for the column
must be specified in the select list in one of these ways:

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"alx" <g...@waymark.net> wrote in message
news:QYEd6.582$v77.1...@dfiatx1-snr1.gtei.net...

[a29379]

Like this:
USE pubs
SELECT
(SELECT COUNT(*) FROM Authors t2
WHERE t2.au_id <= t1.au_id AND au_lname LIKE 'd%' ) AS rownumber
, au_lname
, au_fname
FROM Authors t1
WHERE au_lname LIKE 'd%'
ORDER BY au_id

alx wrote in message ...

[DaveSatz]

something like:

SELECT id = identity(int,1,1), ProjID, Descr
INTO #projects

FROM projects
WHERE EmpID = 123

SELECT *
FROM #projects
--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"alx" <g...@waymark.net> wrote in message
news:QYEd6.582$v77.1...@dfiatx1-snr1.gtei.net...

[alx]

Thanks Keith:

But is there any way to just count rows in a query result without creating a
new table?

Alex

"Keith Kratochvil" <SendT...@novusprintmedia.com> wrote in message
news:#rUg8dviAHA.916@tkmsftngp04...

[Joe Celko]

>> How can I add row numbers to a result of a query: <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Would you work from specifications like this for money?

There is no such thing as a "row number" in SQL. SQL is a set oriented
language and sets have no ordering!! This is the most basic concept of
the relational model.

The second most basic concept is that all relationships (like an
ordering) are shown as values in the columns of tables. The question
then becomes, "How can I get a sequential number in a column, based on
sorting my result set on column x?" I will assume that project_id is a
key.

SELECT (SELECT COUNT(*)
FROM Projects AS P1
WHERE P1.project_id <= P1.project_id) AS row_nbr
P2.project_id, P2.description
FROM Projects AS P2
WHERE emp_id = 123
ORDER BY project_id;

Get a good book on relational databases -- they are not like sequential
file systems, which is what I suspect your mental is.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)

which can be cut and pasted into Query Analyzer is appreciated.

---
Trilogy, FORTUNE, and Goldman Sachs are hosting the E-Business event of
the year. Find out more by visiting www.battleroyale2001.com

Sent via Deja.com
http://www.deja.com/

[Keith Kratochvil]

Count rows?

Sounds like you want the .RowCount (or whatever it is called)

I am not sure how you are retrieving your data (select or stored procedure)
and I do not know where this data is going (asp recordset / component /
client), but does this work?

SELECT * FROM SomeTable
SELECT @@rowcount

Run it from Query Analyzer on some of your tables....
It might be what you are looking for.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"alx" <g...@waymark.net> wrote in message

news:VsFd6.641$v77.1...@dfiatx1-snr1.gtei.net...

[alx]

Thanks a lot!

One problem though:
If you try to order by au_lname
in your example, then your rownumber
will be out of order : (

Any other suggestions??
Alex

"a29379" <a29...@my-deja.com> wrote in message
news:#2Ia1eviAHA.2088@tkmsftngp04...

[Keith Harris]

Thanks for showing us how smart you are, and for the lecture.

"Joe Celko" <71062...@compuserve.com> wrote in message
news:9578r2$nmp$1...@nnrp1.deja.com...

[Steve Dassin]

In article <eJXo18wiAHA.2156@tkmsftngp05>,

"Keith Harris" <kha...@hollywoodreporter.com> wrote:
> Thanks for showing us how smart you are, and for the lecture.

He accidently scratched a friend of mine now the poor
sob is gonna live forever.

[Aaron Bertrand [MVP]]

Actually, I usually find a lot of value in Celko's remarks. They're almost
always right on the money (I think I differ with him a bit on the theory vs.
application of IDENTITY values), they're often funny when you're not the
target, and they're merely a bit humbling when you are. This thread was no
exception, he was simply pointing out the admittedly simple concepts of a
relational model -- albeit with a little more flare and sarcasm than the
next guy. Take it for what it is, an opinion from a very knowledgeable and
bitter person. If you don't want to take his advice, that's fine... but
don't take his comments personally -- they're nothing against you, but more
so against the collective group of less knowledgeable SQL people who
continue to defy the standards by misusing relational/set models, using
proprietary extensions and, in some cases, simply not understanding database
theory before using/developing/selling a database architecture.

www.aspfaq.com

[Steve Dassin]

Hello!...where were you when they started the manhattan project?

Steve

[a29379]

USE pubs
select * from
(

SELECT
(SELECT COUNT(*) FROM Authors t2
WHERE t2.au_id >= t1.au_id AND au_lname LIKE 'd%' ) AS rownumber
, au_lname
, au_fname
FROM Authors t1
WHERE au_lname LIKE 'd%'

--ORDER BY au_id
) as tab1
order by 2

"alx" <g...@waymark.net> wrote in message

news:aGGd6.680$v77.2...@dfiatx1-snr1.gtei.net...