Create an ID Field On the fly in SELECT statement (WITHOUT INSERT)

Basically, I'm trying to do some calculations on a subquery, and in order to pull them off, I need each record that gets returned from the subquery to be numbered, in order.

I've got:

    t_item = '35697-0101'
order by t_date

as my subquery, but it comes back with :
The IDENTITY function can only be used when the SELECT statement has an INTO clause.

Yes, I know i can use temporary tables, but I'm trying to not have to do so.
Is there any other way to create an incrementing field on the fly?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yes, assuming you have a unique ID field (e.g. RecordId):

Give this a try and let me know:

   (SELECT COUNT(1) FROM ttimrp010075 t2 WHERE (t2.t_date = t.t_date and t2.RecordId < t.RecordId) OR (t2.t_date < t.t_date)) AS ID,
FROM ttimrp010075 t
WHERE t.t_item = '35697-0101'
ORDER BY t.t_date, t.RecordId

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
INTO TempTable
FROM ttimrp010075
where t_item = '35697-0101'
order by t_date

select * from TempTable
drop table TempTable
NipsMGAuthor Commented:
hmm, ok I'm confused.

I don't have a unique ID field in that table.  That's why I'm trying to create one on the fly.

Basically, I have to do a running total type calculation, and I want to on the fly add an incrementing integer into the query.

so SELECT t_item, t_date, t_qty

Item     Date   Qty
A          1/5     1500
A          1/5     1700
A          1/6     3800
B          1/6     1400


ID        Item     Date   Qty
1          A          1/5     1500
2          A          1/5     1700
3          A          1/6     3800
4          B          1/6     1400

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sorry i didn't see the question title (WITHOUT INSERT) before posting my previous post.  However the only I know is to insert result into temp table, others probably know a better way
>>  don't have a unique ID field in that table.  That's why I'm trying to create one on the fly. <<
A unique id isn't the necessarily the same as the ID you want to create on the fly - in fact it won't be.

The only ways to do this without having a field in the table that can uniquely identify a row, are:
1) use a temp table
2) generate the auto number id in the front end
Looking at your table structures, you cannot build an ID on the field without using a temp table. You have no way to generate it using my approach, and SQL does not provide a function to generate one for you (it's on everyone's wish list i think!!)
It can be done without a unique field, but not without at least a unique combination of fields.

 select rank=count(*), a1.t_item, a1.t_date, a1.t_qty
 from mytable a1, mytable a2
 where a1.t_item + a1.t_date + a1.t_qty  >= a2.t_item + a2.t_date + a2.t_qty
 group by a1.naam, a1.t_date, a1.t_qty  
 order by 1

works as long as the combination of item, date and qty is unique which probably is not the case....
>> It can be done without a unique field, but not without at least a unique combination of fields. <<
Yes, I should have explicitly said that - I was loosely implying that but for simplicity of explanation I didn't.
Also because the table structures would not give a unique combination, as you say.

NipsMGAuthor Commented:
Not the answer I was hoping for, but at least I know where to go from here, Thanks guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.