NipsMG
asked on
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:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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....
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.
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.
ASKER
Not the answer I was hoping for, but at least I know where to go from here, Thanks guys.
ASKER
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