By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Identity column in query result

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
11 Replies


P: n/a
"Surajit Laha" <s_****@rediffmail.com> wrote in message
news:ba**************************@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
Jul 20 '05 #2

P: n/a
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_****@rediffmail.com> wrote in message
news:ba**************************@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

Jul 20 '05 #3

P: n/a
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

"Surajit Laha" <s_****@rediffmail.com> wrote in message
news:ba**************************@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


Jul 20 '05 #4

P: n/a
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_****@rediffmail.com> wrote in message
news:ba**************************@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

Jul 20 '05 #5

P: n/a
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_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
news:ba**************************@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

Jul 20 '05 #6

P: n/a
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

"Surajit Laha" <s_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
news:ba**************************@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

Jul 20 '05 #7

P: n/a
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" <Sh**********@hotmail.com> wrote in message news:<vo************@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_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
news:ba**************************@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

Jul 20 '05 #8

P: n/a
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_****@rediffmail.com (Surajit Laha) wrote in message news:<ba**************************@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" <Sh**********@hotmail.com> wrote in message news:<vo************@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_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
> news:ba**************************@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

Jul 20 '05 #9

P: n/a
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_****@rediffmail.com> wrote in message
news:ba**************************@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" <Sh**********@hotmail.com> wrote in message

news:<vo************@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_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
> news:ba**************************@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

Jul 20 '05 #10

P: n/a
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***@libero.it (Mauro) wrote in message news:<a2**************************@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_****@rediffmail.com (Surajit Laha) wrote in message news:<ba**************************@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" <Sh**********@hotmail.com> wrote in message news:<vo************@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_****@rediffmail.com> wrote in message
news:ba**************************@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_****@rediffmail.com> wrote in message
> > news:ba**************************@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

Jul 20 '05 #11

P: n/a
[posted and mailed, please reply in news]

Surajit Laha (s_****@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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.