Row number in a select statement

Hi
I am looking for a solution to get an incremental row number along my other
select arguments after joining more than one tables.
Thanks in advance.
Ashokuse pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashok" <akumar@.buildfolio.com> wrote in message
news:uweREUBFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am looking for a solution to get an incremental row number along my
> other
> select arguments after joining more than one tables.
> Thanks in advance.
> Ashok
>
>|||Why can't you do it client-side? That's probably much more efficient
than attempting it in SQL.
If you really must, the following is one example of a SQL-based method
(from Pubs). You've said that more than one table is involved but it's
hard to give a full answer for that more complex scenario without more
info - like the DDL for the tables involved and some sample data to
work with.
SELECT au_id, au_lname, au_fname,
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num
FROM Authors AS A
David Portas
SQL Server MVP
--

Row Level Security

Posting again in hopes that someone has a solution..

I've set up a sales report that is by territory. Two tables one of which has

sales detail records and another table with Sales Rep info, including territory and

login.. The two tables are joined by state. What I need to be able to do is schedule

this report to run on Reporting services(Already setup) and only allow the reps

to view a snapshot, don't want anyone executing the report again. Additionally,

I need them to only see the territory that they are responsible for. Does anyone

have a solution for this.

Thx again

Hello,

If I'm understanding correctly, you want to create one master snapshot report, then have each rep only access a certain subset of the data in the snapshot. Is this correct?

Since the snapshots are static, you won't be able to limit down the results further after the snapshot is created. I think what you'll need to do is setup multiple subscriptions, one for each rep. You can specify the rep as a parameter to the report. Then you can send the snapshot of the report to the specific rep.

Hope this helps.

Jarret

|||

Thanks for the suggestion. I think that your suggestion will work, but with over 300 reps, I would rather not create that many subscriptions. There has got to be away. Cognos powerplan allows the view to be created upfront and then when A report goes into that view it further filters the results. You would think I should be able to do that. What about not using the snapshot?