sql server (alternate): Identity column in query result -- sql server (alternate)

Re: Identity column in query result

s_laha NO[at]SPAM rediffmail.com
10/8/2003 12:53:33 AM

Hi all,

Unfortunately none of them worked!
The serial no. column returned some big values and do not appear
serially.

The last one (using a temp table) is ok, but I do not want to use temp
tables.

Task name may not be unique, there are Task_ID and Task_UID which
forms a composite key for this table.

But how can that help?

Please help/comment.

-surajit

[quoted text, click to view]

Re: Identity column in query result

Shervin Shapourian
10/8/2003 10:30:02 AM

Surajit,

Can you tell us more about the nature of your problem? How big is your
table? Isn't it possible to generate sequence numbers in your front-end
application? Why don't you want to use temporary tables? Is it because of
some technical or performance problem or you just prefer not to use temp
tables?

Shervin

[quoted text, click to view]

Re: Identity column in query result

s_laha NO[at]SPAM rediffmail.com
10/8/2003 9:16:36 PM

Hi,

The table has 25000 rows at this moment and increasing.

I can obviously generate a sequence in the front end, but:
1) I want to do it in the back end, to check if it is possible
2) I dont want to use Temp tables

If I accept any of these, I can solve this problem right away.

But I want to find an option of doing it in the backend, following
these constraints, and want to know if it is possible.

If it is not possible, then it is important for me to know that it is
NOT POSSIBLE.

The description of the table does not help much, as I would like to
use this idea (if I get any) in any query result from any table.

Thanks,

-surajit

[quoted text, click to view]

Re: Identity column in query result

John Bell
10/8/2003 11:28:23 PM

Hi

It would help if you posted DDL (Create table statements), example data
(Insert Statements) and your own attempts to solve the problem, then
everyone would have a clear understanding of your problem actually is.

John

[quoted text, click to view]

Re: Identity column in query result

mj_23 NO[at]SPAM libero.it
10/9/2003 6:53:17 AM

Hi,
I don't think you can get a serial number with a simple SELECT, unless
you migrate on Oracle RDBMS which has a pseudo-column named ROWNUM
very useful for your purpose; rather, on MS-SQL 2K, you can create a
function that return a table. This example run on Northwind sample
database:

######

create function dbo.FN_ORDERS()
returns @tab table (
ROWNUM int,
OrderId int,
OrderDate datetime,
ShipName nvarchar(40)
) as
begin
declare @OrderId int
declare @OrderDate datetime
declare @ShipName nvarchar(40)
declare @i int
declare c cursor for
select OrderId, OrderDate, ShipName from Northwind.dbo.Orders
set @i = 0
open c
fetch c into @OrderId, @OrderDate, @ShipName
while @@fetch_status = 0 begin
set @i = @i + 1
insert @tab values (@i, @OrderId, @OrderDate, @ShipName)
fetch c into @OrderId, @OrderDate, @ShipName
end
close c
deallocate c
return
end
go

select * from dbo.FN_ORDERS()

######

Obviously you have to create one function per table and change your
front end code...

Bye.

[quoted text, click to view]

Re: Identity column in query result

Shervin
10/10/2003 2:01:31 AM

Surajit,

I'm afraid you can't find a straight forward solution for this problem. If
you really want to do this without a temp table and on the back end, the
only other way that I can think about is a self-join and counting the
records (as both Johns posted), but this solution is not efficient on large
result sets.

I wish SQL-Server had a ROWNUM pseudo column like Oracle.

Shervin

[quoted text, click to view]

Re: Identity column in query result

s_laha NO[at]SPAM rediffmail.com
10/10/2003 2:24:56 AM

Hi,

Thanks for youir solution.
Although it uses the idea of inserting the data in a table, it is a good solution.

So, a simple SELECT can not do the trick.

Thanks a lot to you all, who contributed, and helped me a lot!

regards,
-surajit

[quoted text, click to view]

Re: Identity column in query result

Erland Sommarskog
10/11/2003 10:39:56 PM

[posted and mailed, please reply in news]

Surajit Laha (s_laha@rediffmail.com) writes:
[quoted text, click to view]

Actually it can. See the script below. However, the performance is likely
to be bad. Using a temp table with an identity column would be a lot faster.

CREATE TABLE surajit (taskid int NOT NULL,
taskuid int NOT NULL,
taskname varchar(12) NOT NULL,
startdate datetime NOT NULL,
PRIMARY KEY (taskid, taskuid))
go
INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 1, 'Task 1', '20021212')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (1, 3, 'Task 1.2', '20021224')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (2, 1, 'Task 2', '20030605')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 1, 'Task 3', '20010915')

INSERT surajit (taskid, taskuid, taskname, startdate)
VALUES (3, 2, 'Task 3', '20011015')

go
SELECT taskname, startdate,
cnt = (SELECT COUNT(*)
FROM surajit b
WHERE b.taskname < a.taskname OR
(b.taskname = a.taskname AND
b.taskid < a.taskid OR
(b.taskid = a.taskid AND
b.taskuid < a.taskuid))) + 1
FROM surajit a
ORDER BY cnt

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.