Check Sequence - 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 Administration (2000)

Check Sequence

Author

Topic

sr_aneesh
Starting Member

17 Posts

Posted - 2005-01-01 : 12:52:00

i have following table

Id Name Rank
1 Tom 1
2 Jack 2
3 Austin 3
4 Stel 9
5 Stel 5
6 Mark 6
7 Job 7
8 Tom 4
9 Mark 2
10 Stel 4
11 Tom 5
12 Job 1
13 Tom 8
14 Job 5
15 Stel 1

How do i view the name of people whos rank order is not proper. For Eg : Toms Max Rank is 8.i want that if the other rank of Tom is not between 1 to 7 then it should show up toms name

Thanx in Advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-01 : 13:56:33

declare @rank1 int
declare @rank2 int
select @rank1 = 1, @rank2 = 7

select *
from MyTable
where Rank between @rank1 and @rank2 -- or any other rank you like

if that's not what you need explain the logic behind your problem.

didn't think anyone is doind anything constructive on january 1st...

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-01 : 14:13:53

Do you mean you want the names of all the people who don't have a rank lower than their max rank?
That would b all the people with a single value rank - and greater than 1 maybe.

select name
from tbl
group by name
having count(distinct rank) = 1
and max(rank) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sr_aneesh
Starting Member

17 Posts

Posted - 2005-01-02 : 00:21:29

thanx a lot... will try and let u know

sr_aneesh
Starting Member

17 Posts

Posted - 2005-01-02 : 01:13:52

sorry that dint help.. hers in detail what i my query is ...

Thers are three buses running for a school. Bus No's are 1,2 and 3.
for eg :

Bus No.1 should travel via the following stops in a given sequence

Stop Name Sequence
Stop1 1
Stop2 2
Stop3 3
Stop4 4
Stop5 5

Bus No.2 should travel via the following stops in a given sequence

Stop Name Sequence
Stop1 1
Stop4 2
Stop2 3
Stop3 4
Stop5 5

Bus No.3 should travel via the following stops in a given sequence

Stop Name Sequence
Stop4 1
Stop3 2
Stop2 3
Stop5 4
Stop1 5

Whenever a bus touch any stop the record and time of the bus arrival is been entered in to system

now i want that when the record is entered it should check if the particular bus has come through the proper sequence which means it has not skipped the

pervious stop.
if it has been skipped then record should not be inserted...

how do i check this in SQL???

Can some one give me a rough idea how to tacke this ....

Thanx in Advance

jhermiz

3564 Posts

Posted - 2005-01-03 : 14:02:56

Shouldnt this be done through your front end and not your db tier ? Or at least to me its your business logic and should be thrown in a middle tier. It really isnt hard to check a sequence given you are provided one, which you are.
A simple check of each step using returned results or even since your case is simple using some conditional if statements.

It really doesn't sound like an SQL problem, more of a business logic problem.

A new beat on the web -- http://www.web-impulse.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-03 : 14:48:50

try this:


declare @PrevStopName varchar(50)
set @PrevStopName = 	(select top 1 *
			from	MyTable
			where	sequence < (select sequence from MyTable where	stopName = @CurrentStopName)
			order by sequence desc)
--or
set @PrevStopName = 	(select 	top 1 t1.*
                         from	@MyTable t1 
                           inner join @MyTable t2                     
                                on t1.sequence < t2.sequence and 
                                t2.stopName = @CurrentStopName)
                          order by t1.sequence desc)

if @PrevStopName = @CurrentStopName
begin
	-- insert data into table
end

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 16:52:08

see
http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

>> Shouldnt this be done through your front end and not your db tier ? Or at least to me its your business logic and should be thrown in a middle tier.

lol - It's been a while since I've heard that little chestnut.
in fact I'll add it to
http://www.mindsdoor.net/SQLAdmin/BadThings.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-03 : 16:57:25

YOu need a stop table, and then left join to it

What's a middle tier?

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-03 : 17:17:17

>> What's a middle tier?
Number all your tiers in order.
If there is an even number then add a tier or remove one.
The middle one will be that with the number {max(tierno) + 1) / 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sr_aneesh
Starting Member

17 Posts

Posted - 2005-01-04 : 03:13:04

Thanx a lot i will try this and get back ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources