Identity column: Identity column in query result

I am firing a query like:

SELECT TaskName, StartDate FROMTasks
WHERE StartDate >= '01-Aug-2003'

Now the result comes as:

TaskName StartDate
--------
Task1 01-Aug-2003
Task5 10-Oct-2003

etc.

Now what I want is this:

Sl. No. TaskName StartDate
-----------
1 Task1 01-Aug-2003
2 Task5 10-Oct-2003

How do I get the Sl. No. field (it does not exist in the table).
Can I do it?

-surajit"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit

SELECT T1.StartDate, T1.TaskName, COUNT(*) AS Cnt
FROM Tasks AS T1
INNER JOIN
Tasks AS T2
ON T2.StartDate <= T1.StartDate AND
T1.StartDate >= '20030801' AND
T2.StartDate >= '20030801'
GROUP BY T1.StartDate, T1.TaskName

Regards,
jag|||Hi

If TaskName is unique and gives you the correct order then try:

select ( SELECT COUNT(*) FROM tmp c WHERE c.TaskName <= b.TaskName ) as id,
b.TaskName, b.Startdate
from Tasks b
order by TaskName

John

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> I am firing a query like:
> SELECT TaskName, StartDate FROMTasks
> WHERE StartDate >= '01-Aug-2003'
> Now the result comes as:
> TaskName StartDate
> --------
> Task1 01-Aug-2003
> Task5 10-Oct-2003
> etc.
> Now what I want is this:
>
> Sl. No. TaskName StartDate
> -----------
> 1 Task1 01-Aug-2003
> 2 Task5 10-Oct-2003
>
> How do I get the Sl. No. field (it does not exist in the table).
> Can I do it?
> -surajit|||Surajit,

I'm not saying this is a better way, it's just another option:

SELECT identity(int) as Sl,
TaskName,
StartDate
INTO #Tmp
FROM Tasks
WHERE StartDate >= '01-Aug-2003'

SELECT * FROM #Tmp

DROP TABLE #Tmp

Shervin

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

> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > I am firing a query like:
> > SELECT TaskName, StartDate FROMTasks
> > WHERE StartDate >= '01-Aug-2003'
> > Now the result comes as:
> > TaskName StartDate
> > --------
> > Task1 01-Aug-2003
> > Task5 10-Oct-2003
> > etc.
> > Now what I want is this:
> > Sl. No. TaskName StartDate
> > -----------
> > 1 Task1 01-Aug-2003
> > 2 Task5 10-Oct-2003
> > How do I get the Sl. No. field (it does not exist in the table).
> > Can I do it?
> > -surajit|||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

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> 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
>
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > I am firing a query like:
> > > > SELECT TaskName, StartDate FROMTasks
> > > WHERE StartDate >= '01-Aug-2003'
> > > > Now the result comes as:
> > > > TaskName StartDate
> > > --------
> > > Task1 01-Aug-2003
> > > Task5 10-Oct-2003
> > > > etc.
> > > > Now what I want is this:
> > > > > Sl. No. TaskName StartDate
> > > -----------
> > > 1 Task1 01-Aug-2003
> > > 2 Task5 10-Oct-2003
> > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > Can I do it?
> > > > -surajit|||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

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

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> 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
> "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > 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
> > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > I am firing a query like:
> > > > > > SELECT TaskName, StartDate FROMTasks
> > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > Now the result comes as:
> > > > > > TaskName StartDate
> > > > --------
> > > > Task1 01-Aug-2003
> > > > Task5 10-Oct-2003
> > > > > > etc.
> > > > > > Now what I want is this:
> > > > > > > > Sl. No. TaskName StartDate
> > > > -----------
> > > > 1 Task1 01-Aug-2003
> > > > 2 Task5 10-Oct-2003
> > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > Can I do it?
> > > > > > -surajit|||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.

s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> 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
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > 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
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > 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
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||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

"Surajit Laha" <s_laha@.rediffmail.com> wrote in message
news:ba8ee108.0310082016.4fe9bca5@.posting.google.c om...
> 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
>
>
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:<vo8icukkap6l9a@.corp.supernews.com>...
> > 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
> > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > 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
> > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > I am firing a query like:
> > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > Now the result comes as:
> > > > > > > > TaskName StartDate
> > > > > --------
> > > > > Task1 01-Aug-2003
> > > > > Task5 10-Oct-2003
> > > > > > > > etc.
> > > > > > > > Now what I want is this:
> > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > -----------
> > > > > 1 Task1 01-Aug-2003
> > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > Can I do it?
> > > > > > > > -surajit|||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

mj_23@.libero.it (Mauro) wrote in message news:<a2af5c1f.0310090553.279c32c7@.posting.google.com>...
> 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.
>
> s_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0310082016.4fe9bca5@.posting.google.com>...
> > 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
> > "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message news:<vo8icukkap6l9a@.corp.supernews.com>...
> > > 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
> > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > news:ba8ee108.0310072353.35716e92@.posting.google.c om...
> > > > 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
> > > > > > > > > "Surajit Laha" <s_laha@.rediffmail.com> wrote in message
> > > > > news:ba8ee108.0310070108.28e3c605@.posting.google.c om...
> > > > > > I am firing a query like:
> > > > > > > > > > SELECT TaskName, StartDate FROMTasks
> > > > > > WHERE StartDate >= '01-Aug-2003'
> > > > > > > > > > Now the result comes as:
> > > > > > > > > > TaskName StartDate
> > > > > > --------
> > > > > > Task1 01-Aug-2003
> > > > > > Task5 10-Oct-2003
> > > > > > > > > > etc.
> > > > > > > > > > Now what I want is this:
> > > > > > > > > > > > > > Sl. No. TaskName StartDate
> > > > > > -----------
> > > > > > 1 Task1 01-Aug-2003
> > > > > > 2 Task5 10-Oct-2003
> > > > > > > > > > > > > > How do I get the Sl. No. field (it does not exist in the table).
> > > > > > Can I do it?
> > > > > > > > > > -surajit|||[posted and mailed, please reply in news]

Surajit Laha (s_laha@.rediffmail.com) writes:
> 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.

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
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, February 24, 2012

Identity column in query result

No comments:

Post a Comment

Identity column

Blog Archive

About Me