Morning Brain Teaser - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Morning Brain Teaser

Author

Topic

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2001-12-13 : 12:07:32

Ok all you set based yak guru's... it's time to show an old cursor fiend the light. I could do this in a couple min with a cursor, and it is a one time only process so performace isn't really a concern, but I want to know how to do this in a set so I can be a part of the cool crowd. Here's the problem:

I have a complaint table that looks a little like this.

COMPLAINT_NUMBER [VARCHAR](20)
DETAIL_ID [NUMERIC](18,0)
REVIEW_LEVEL_ID [NUMERIC](18,0)

These three fields constitute a unique key. I want to renumber the detail_id field so that it starts from 1 for every complaint. That way COMPLAINT_NUMBER AND DETAIL_ID will be a unique ID and will have the same order as this. Here is some sample data as it looks now.

CCI-1999-36085 153135 1
CCI-1999-36085 153135 2
CCI-1999-36269 153230 1
CCI-1999-36269 153360 1
CCI-1999-36269 153360 2
CCI-1999-36269 153360 3
CCI-1999-36269 153360 4
CCI-1999-36271 153231 1
CCI-1999-36271 153231 2

And here is how I would like it to look when we're done.

CCI-1999-36085 1 1
CCI-1999-36085 2 2
CCI-1999-36269 1 1
CCI-1999-36269 2 1
CCI-1999-36269 3 2
CCI-1999-36269 4 3
CCI-1999-36269 5 4
CCI-1999-36271 1 1
CCI-1999-36271 2 2

Please let me know if you need further explanation or information.

Thanks!

Jeff Banschbach
Consultant, MCDBA

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-13 : 14:09:06

So that's


CREATE TABLE COMPLAINTS (
  COMPLAINT_NUMBER varchar(20),
  DETAIL_ID numeric(18,0),
  REVIEW_LEVEL_ID numeric(18,0),
  PRIMARY KEY CLUSTERED (COMPLAINT_NUMBER, DETAIL_ID, REVIEW_LEVEL_ID)
)


INSERT INTO COMPLAINTS VALUES ('CCI-1999-36085', 153135, 1)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36085', 153135, 2)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36269', 153230, 1)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36269', 153360, 1)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36269', 153360, 2)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36269', 153360, 3)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36269', 153360, 4)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36271', 153231, 1)
INSERT INTO COMPLAINTS VALUES ('CCI-1999-36271', 153231, 2)

Yes? This sort of thing should do it:


UPDATE COMPLAINTS SET DETAIL_ID = (
  SELECT COUNT(*)+1
  FROM COMPLAINTS C2
  WHERE C2.COMPLAINT_NUMBER = COMPLAINTS.COMPLAINT_NUMBER
    AND (C2.DETAIL_ID < COMPLAINTS.DETAIL_ID
      OR (C2.DETAIL_ID = COMPLAINTS.DETAIL_ID
        AND C2.REVIEW_LEVEL_ID < COMPLAINTS.REVIEW_LEVEL_ID)))

Blimey, what a lot of shouting!

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2001-12-13 : 15:44:46

That's perfect. I was a lot closer than I thought. I just had an AND and an OR reversed. Thanks, you do the Yaks much credit.

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 12/13/2001 15:45:05

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources