Identity column in query result

[Surajit Laha]

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

[John Gilson]

"Surajit Laha" <s_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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

[John Bell]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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

[Shervin Shapourian]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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

[Shervin Shapourian]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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

[John Bell]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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]

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**********@h otmail.com> wrote in message news:<vo******* *****@corp.supe rnews.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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...
> 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

[Mauro]

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.O rders
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_****@rediffma il.com (Surajit Laha) wrote in message news:<ba******* *************** ****@posting.go ogle.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**********@h otmail.com> wrote in message news:<vo******* *****@corp.supe rnews.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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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_****@rediffm ail.com> wrote in message
> news:ba******** *************** ***@posting.goo gle.com...
> > 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

[Shervin]

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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.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**********@h otmail.com> wrote in message

news:<vo******* *****@corp.supe rnews.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_****@rediffm ail.com> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

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_****@rediffm ail.com> wrote in message
> news:ba******** *************** ***@posting.goo gle.com...
> > 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