Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-02-25
9
Medium Priority
?
635 Views
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:

SELECT IDENTITY(int, 1,1) AS ID, *
FROM
    ttimrp010075
WHERE
    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?

--NipsMG
0
Comment
Question by:NipsMG
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 23

Accepted Solution

by:
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
   (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,
    t.*
FROM ttimrp010075 t
WHERE t.t_item = '35697-0101'
ORDER BY t.t_date, t.RecordId
0
 
LVL 12

Assisted Solution

by:gbzhhu
gbzhhu earned 400 total points
ID: 13403090
SELECT  IDENTITY(int, 1,1) AS ID, *
INTO TempTable
FROM ttimrp010075
where t_item = '35697-0101'
order by t_date

select * from TempTable
drop table TempTable
0
 

Author Comment

by:NipsMG
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
FROM
TABLE

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


Becomes

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


0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 12

Expert Comment

by:gbzhhu
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
0
 
LVL 23

Expert Comment

by:adathelad
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
0
 
LVL 23

Expert Comment

by:adathelad
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!!)
0
 
LVL 1

Expert Comment

by:pietjepuk
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....
0
 
LVL 23

Expert Comment

by:adathelad
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.

0
 

Author Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

577 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