sql server 2000 row number generate

2 августа 2012 г. 14:03

In the following query I need to generate row numbers based on call_id. I am running this query against SQL server 2000 so cannot use row_number function.

SELECT

CLS.CALL_ID AS CALL_ID

--here is I need to generate ORDINAL

,ISNULL(CTE.LOG_DATETIME, CLS.LOG_DATETIME) AS LOG_DT

,DEPT_ID

,FUNCTION_ID

, CASE

WHEN SRC = 'CDLS' THEN DETAIL_INFO

ELSE ''

END AS DETAIL_INFO

, END_DATE AS CALL_END_DT

FROM

#TEMP_CTE AS CTE

INNER

JOIN DBO.CALL_LOG AS CLS

ON CTE.CALL_ID=CLS.CALL_ID

WHERE

NOT EXISTS (SELECT * FROM MCCS_IVR.vru.call_function_log AS VCL

WHERE VCL.call_id=CLS.call_id)

ORDER

BY CTE.LOG_DATETIME the output shoul look like

call_end_dt	call_id	ordinal	log_dt	dept_id	function_id	detail_info
7/17/12 4:03	113141120717040205	1	7/17/12 4:02	22	962
7/17/12 4:03	113141120717040205	2	7/17/12 4:02	22	222
7/17/12 4:03	113141120717040205	3	7/17/12 4:02	22	2200
7/17/12 4:03	113141120717040205	4	7/17/12 4:02	22	2202
7/17/12 4:03	113141120717040205	5	7/17/12 4:02	22	860
7/17/12 4:03	113141120717040205	6	7/17/12 4:02	22	97
7/17/12 4:03	113141120717040205	7	7/17/12 4:03	22	855
7/17/12 4:03	113141120717040205	8	7/17/12 4:03	22	510
7/17/12 4:03	113141120717040205	9	7/17/12 4:03	22	327
7/17/12 4:03	113141120717040205	10	7/17/12 4:03	22	-1

Pls help...

Thanks,

harry

hsbal

Все ответы

2 августа 2012 г. 14:12

Модератор

0

I would suggest to consider if this column is really needed. One way to accomplish this in SS 2000 is using a correlated query, but performance for this approach is ugly.

Example:

declare @T table (c1 int not null unique);

insert into @T (c1)
select 4 as c1 union all
select 6 as c1 union all
select 17 as c1;

select c1, (select count(*) from @T as B where B.c1 <= A.c1) as c2
from @T as A
order by c1;
GO

AMB

Some guidelines for posting questions...
- Ответить
- Цитировать
2 августа 2012 г. 14:29

0

Try

with set1 as
(SELECT CTE.LOG_DATETIME,
CLS.CALL_ID AS CALL_ID
--here is I need to generate ORDINAL
,ISNULL(CTE.LOG_DATETIME, CLS.LOG_DATETIME) AS LOG_DT
,DEPT_ID
,FUNCTION_ID
, CASE
WHEN SRC = 'CDLS' THEN DETAIL_INFO
ELSE ''
END AS DETAIL_INFO
, END_DATE AS CALL_END_DT
FROM
#TEMP_CTE AS CTE
INNER
JOIN DBO.CALL_LOG AS CLS
ON CTE.CALL_ID=CLS.CALL_ID
WHERE
NOT EXISTS (SELECT * FROM MCCS_IVR.vru.call_function_log AS VCL
WHERE VCL.call_id=CLS.call_id)
ORDER
BY CTE.LOG_DATETIME)
select (select count(*) from set1 b where b.call_id=a.call_id
and b.LOG_DATETIME<=a.LOG_DATETIME) ordinal,*
from set1 a;
go

Many Thanks & Best Regards, Hua Min
- Ответить
- Цитировать
2 августа 2012 г. 15:01

0

Thanks Chen for ur reply. Actually I am new to SQL. I am not getting how to make set 1. Though it is there, I am not getting it sytax wise.

Thanks,

harry
hsbal
- Ответить
- Цитировать
2 августа 2012 г. 15:12

Модератор

0

He/She is using a common table expression, but this feature was introduced with SS 2005, so you will have to use a derived table or a view.

create view dbo.my_vw
as
your query goes here
GO
select *, (select count(*) from dbo.my_vw as B where B.call_id = A.call_id and B.log_datetime <= A.log_datetime) as ordinal
from dbo.my_vw as A
order by call_id, log_datetime;
GO

If you can have ties by (call_id, log_datetime) then you will need another column(s) to break the tie.

AMB

Some guidelines for posting questions...
- Ответить
- Цитировать
3 августа 2012 г. 1:26

0

Thanks Chen for ur reply. Actually I am new to SQL. I am not getting how to make set 1. Though it is there, I am not getting it sytax wise.

Thanks,

harry

hsbal

You can run it which should be fine.
Many Thanks & Best Regards, Hua Min
- Ответить
- Цитировать

3 августа 2012 г. 4:52

Can you try the below:

Create Table MSDN_Test1(Col1 Varchar(10))
Insert into MSDN_Test1 Select 'Latheesh'
Insert into MSDN_Test1 Select 'NK'
--Method -1 
/*
	You need to insert into a temp table here ("into" usage is must here)
*/
select identity(int, 1,1) myNo , Col1 
      into #Temp 
   from MSDN_Test1
Select * From #Temp
Drop table #Temp
--Method -2
/*
	I dont prefer this as it has some fuzzy logic.
*/
select (select count(*)   from MSDN_Test1 as e2
          where e2.Col1 <= e1.Col1)  as myNo, e1.Col1
from MSDN_Test1 as e1
Drop table MSDN_Test1

Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

3 августа 2012 г. 6:42

Модератор

The following article deals with sequence generation:

http://www.sqlusa.com/bestpractices2005/sequence/

I vote for the IDENTITY function:

SELECT ID=IDENTITY(int,1,1), 
ProductName, UnitPrice, UnitsInStock
INTO #Product
FROM Northwind.dbo.Products
ORDER BY ProductName;

SELECT * FROM #Product ORDER BY ID;
/*
1	Alice Mutton	39.00	0
2	Aniseed Syrup	10.00	13
3	Boston Crab Meat	18.40	123
4	Camembert Pierrot	34.00	19
5	Carnarvon Tigers	62.50	42
6	Chai	18.00	39
7	Chang	19.00	17
8	Chartreuse verte	18.00	69
9	Chef Anton's Cajun Seasoning	22.00	53
10	Chef Anton's Gumbo Mix	21.35	0
....*/

Kalman Toth SQL SERVER 2012 & BI TRAINING

3 августа 2012 г. 14:26

Модератор

0
Kalman,

I would suggest to check these articles.

IDENTITY() Function Isn't Reliable for Imposing Order on a Result Set
http://www.sqlmag.com/article/sql-server/identity-function-isn-t-reliable-for-imposing-order-on-a-result-set

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause
http://support.microsoft.com/kb/273586

If you are going to use similar approach, I would suggest using:

insert into T(c1,..., cn)
select c1,..., cn
from R
order by c3;

AMB

Some guidelines for posting questions...
- Изменено HunchbackMVP, Moderator 3 августа 2012 г. 14:27
- Помечено в качестве ответа Iric WenModerator 15 августа 2012 г. 1:19
- Ответить
- Цитировать
5 августа 2012 г. 6:28

Модератор

0
Thanks Alejandro.

Surprising, one would expect logical implementation of logical queries.

How about this one?
```
SELECT 
	ProductName, UnitPrice, UnitsInStock
INTO Product
FROM Northwind.dbo.Products
ORDER BY ProductName;
GO
ALTER TABLE Product ADD ID INT IDENTITY(1,1);
GO
```
A stronger version?
```
SELECT 
	ProductID = convert(int,ProductID), ProductName, UnitPrice, UnitsInStock
INTO Product
FROM Northwind.dbo.Products
ORDER BY ProductName;
GO
ALTER TABLE Product ADD CONSTRAINT pkProd PRIMARY KEY (ProductName);
GO
ALTER TABLE Product ADD ID INT IDENTITY(1,1);
GO
```
SQL Server 2000 article on sequencing:

http://www.sqlusa.com/bestpractices/sequencesubset/

Kalman Toth SQL SERVER 2012 & BI TRAINING
- Изменено Kalman TothMicrosoft Community Contributor, Moderator 5 августа 2012 г. 6:44
- Ответить
- Цитировать
5 августа 2012 г. 9:06

0

Surprising, one would expect logical implementation of logical queries.

The logic of IDENTITY is to be a auto-generated number, nothing else. The fact that it's appears ordered is only because there are better odds for uniqueness than with a random number.

How about this one?

Both methods with ALTER TABLE rely on chance.

What possibly works is to create a temp table with an IDENTITY column with CREATE TABLE and insert into to it with ORDER BY, adding OPTION (MAXDOP 1). At least I think the odds are better than with SELECT INTO. But it's questionable whether it is guaranteed to work.

Given the poor performance of the correlated subquery, you may prefer to take that risk.

Then again, if you also need partitioning in the numbering, the IDENTITY solution requires an extra step, so you may prefer the subquery after all.

Of course, the best long-term solution is to upgrade...

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Ответить
- Цитировать
5 августа 2012 г. 15:38

Модератор

0

Both methods with ALTER TABLE rely on chance.

What possibly works is to create a temp table with an IDENTITY column with CREATE TABLE and insert into to it with ORDER BY, adding OPTION (MAXDOP 1). At least I think the odds are better than with SELECT INTO. But it's questionable whether it is guaranteed to work.

Thanks Erland. Good to know!

ROW_NUMBER() to the rescue.

Kalman Toth SQL SERVER 2012 & BI TRAINING
- Ответить
- Цитировать