Sequence Numbers - 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

SQL Server Development (2000)

Sequence Numbers

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-14 : 17:39:56

Otay,

I am writing some views for a third party product. (sql 2k)

There are some groups of people that are being selected in a separate view, this corresponding view shows addt'l info:
the 0-N times of the day they smoke crack. But the third party wants
to have a sequence number with each time they smoke crack.

So
PersonID SmokeTime Sequence #
22 11:00 1
22 1:00 2
22 3:00 3
25 10:00 1

Is there any fancy way to do this via a view?

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-14 : 17:58:11

Quick! Somebody get Graz to help out with this problem.

Sam

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-14 : 18:09:59

Has he written something on the subject, I have been doing some digging
and haven't found much?

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-14 : 18:51:45

I think you'll need to add a column and update it with serial values. There's an example of how to do this in the article on "removing duplicates".

Sam

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-07-14 : 19:26:49

Got Keys?

Given that PersonID and SmokeTime is the key....


create table Crack(PersonID INT NOT NULL, SmokeTime DATETIME NOT NULL PRIMARY KEY(PersonID, SmokeTime))
go
Insert Crack 
	Select 22, '11:00'
	UNION ALL
	Select 22 ,'1:00'
	UNION ALL 
	Select 22 ,'3:00'
	UNION ALL
	Select 25, '10:00'
	UNION ALL
	Select 25 ,'11:00'
	UNION ALL 
	Select 26 ,'3:00'
go
SELECT C.PersonID, X.SmokeTime, COUNT(*) AS SequenceNo
FROM Crack C CROSS JOIN Crack X
WHERE X.PersonID = C.PersonID AND X.SmokeTime >= C.SmokeTime
GROUP BY C.PersonID, X.SmokeTime

DavidM

"SQL-3 is an abomination.."

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-16 : 12:44:48

Whoa,

I finally got to fiddle with that SQL.

You Rock! or Polka or Waltz (whatever your pleasure).

That is interesting sql. Thankfully I can make sense of it.

I will tip a beer your way for the help.

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-07-16 : 13:58:38

Personally I think the second party is doing the crack smokeing.

Jim
Users <> Logic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-07-16 : 14:29:54

In that case, I would be even more aloof.

or whatever crack does to a person.

________________________________________________
(Beer + Beer + Beer + Beer + Beer + Martini w/French Vodka + Beer + Beer + Beer) = Sick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources