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

Posted on 2005-02-25
Medium Priority
Last Modified: 2008-02-01
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?

Question by:NipsMG
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
LVL 23

Accepted Solution

adathelad earned 600 total points
ID: 13402769
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
LVL 12

Assisted Solution

gbzhhu earned 400 total points
ID: 13403090
INTO TempTable
FROM ttimrp010075
where t_item = '35697-0101'
order by t_date

select * from TempTable
drop table TempTable

Author Comment

ID: 13403127
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

LVL 12

Expert Comment

ID: 13403178
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
LVL 23

Expert Comment

ID: 13403226
>>  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
LVL 23

Expert Comment

ID: 13403253
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!!)

Expert Comment

ID: 13404332
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....
LVL 23

Expert Comment

ID: 13404437
>> 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.


Author Comment

ID: 13406661
Not the answer I was hoping for, but at least I know where to go from here, Thanks guys.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question