473,836 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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


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?

Jul 20 '05
11 13756

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!


mj***@libero.it (Mauro) wrote in message news:<a2******* *************** ****@posting.go ogle.com>...
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


create function dbo.FN_ORDERS()
returns @tab table (
OrderId int,
OrderDate datetime,
ShipName nvarchar(40)
) as
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
close c
deallocate c

select * from dbo.FN_ORDERS()

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

s_****@rediffma il.com (Surajit Laha) wrote in message news:<ba******* *************** ****@posting.go ogle.com>...

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

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.



"Shervin Shapourian" <Sh**********@h otmail.com> wrote in message news:<vo******* *****@corp.supe rnews.com>...

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


"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

Jul 20 '05 #11
[posted and mailed, please reply in news]

Surajit Laha (s_****@rediffm ail.com) writes:
Thanks for youir solution.
Although it uses the idea of inserting the data in a table, it is a good

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))
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')

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
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
Jul 20 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Gunnar Vøyenli | last post by:
Hi (SQL Server 2000) I have an existing table (t) with a column that is NOT an identity column (t.ID), but it has manually inserted "row numbers". I want to make this column become an identity column. This column is a key field to other tables, so I want to keep the row numbers that are allready inserted. From the Query Analyzer, how do I do this?
by: Dave | last post by:
I cannot insert into my appointments table because the primary key and identity column, appt_id, cannot be added. What do I have to change in my SQL statement to add new records into this table? I'm using SQL Server 2000 BE with Access Data Project FE. tbl_appointment ------------------- 1. appt_id (pk) --- identity column, seed 25, increment 1 2. date_id 3. time_start 4. time_end 5. appt_details 6. lkp_emp_id
by: JJA | last post by:
I have fixed length records (167 bytes) in a .txt file and want to load this data exactly as is into a staging table where I hope to be able to later get at selected columns using the SUBSTRING function. Here is my target table: CREATE TABLE JJA_BCP_NHO_DAT ( RecID int IDENTITY(1,1) NOT NULL , Data VARCHAR(167) NOT NULL ) Here is my bcp command: bcp DevMDW.dbo.JJA_BCP_NHO_DAT in
by: mal_k100 | last post by:
Hi All, 1. Created table in SQL Server 2K with Primary key as int Identity 2. Link to table in MS Access 2K 3. Using form in MSAccess to update the linked table I want SQL server to automatically update the int identity column as it would normally. i.e. no need to enter a value even though it is a NOT NULL field. SQL Server recognises this , allows the row details to be entered without the int identity and creates it when moving to the...
by: WhiteEagl | last post by:
Hello, I would need some help with this identity column problem in SQLServer. I have a database with two tables. The Parent table has an Identity column. Parent (ParentID IDENTITY, Name) Child (ChildID, Name, ParentID)
by: Jason L James | last post by:
Hi all, I recently wrote a vb.net app using oledb to an access database. When I inserted new rows in my datatable the identity column was automatically created. This app used an un-typed dataset. My current app is using sqlClient and a typed dataset that I created by exporting an xsd
by: Hongyu Sun | last post by:
Hi, All: Please help. I use sql server as back end and Access 2003 as front end (everything is DAO). A table on SQL server has an identity column as the key. We have trouble on adding records to this table using the following SQL. strSQL = "INSERT INTO myTableOnSQLServer (A, B, C, D, E) SELECT A, B, C, D,
by: Praveen_db2 | last post by:
Hi all Db2 8.1.3 Windows I have to load a table with an identity column with GENERATED ALWAYS option. I have to load the data in the identity column as it is. I created the table and tried this command db2 load from tab110.ixf of ixf modified by generatedoverride insert into wsaces.tabname for exception wsaces.tabname_excp
by: shenanwei | last post by:
I have 2 same windows machine, same instance configure and Database , all run DB2 UDB V8.1.5 Test 1 : create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)); insert into out_1 (line) values ('C000000002XYTNF102020201855000000075000519600040547000003256510 0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.