SQL problem - 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

General SQL Server Forums

New to SQL Server Programming

SQL problem

Author

Topic

darius12
Starting Member

16 Posts

Posted - 2011-12-04 : 11:16:14

Can anyone write me code for comparing two numbers. The thing is i have an assignment to write a code which checks if there are gaps in numbers.
I have an idea to count rows in column numbers, and to get the max number from the numbers column, but i dont know how to compare it!
So i have column with 1,2,3,4,5,9 and i would be comparing 9 and 6, so they are not eaqual, there are gaps. Any help would come great! my code for counting is SELECT count(number) AS d1 FROM diplomas and for max number is MAX(number + 1) ..thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 11:51:10

so what should be the output? count of gaps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2011-12-04 : 11:53:05

quote:
Originally posted by visakh16

so what should be the output? count of gaps?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Just true or false for gaps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 11:55:18

hmm? can you show your required output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2011-12-04 : 12:10:30

i have nothing on my mind, just char true or false for example

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 12:15:53

hmm? that doesnt make any sense to me
if you dont have any idea, how do you expect us to say what you want?
anyways what i think would be sensible output is

number gap

with yes/no againt each number

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2011-12-04 : 12:23:44

ok, i have column with sorted numbers, my query should check that column for gaps and if there are gaps, it should notify me on whatever way (returns a message, or returns false)

darius12
Starting Member

16 Posts

Posted - 2011-12-04 : 12:24:43

it doesnt need to find gaps, just send a single message at the end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-04 : 12:38:19

[code]
select count(case when t.number-t1.number> 1 then 1 else null end) as gapcount
from table t
cross apply (select top 1 number
from table
where number > t.number
order by number)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2011-12-05 : 07:30:33

SELECT count(number) AS d1, MAX(number + 1) as d2 FROM diplomas WHERE d2.number = d1.number

This is code in postgresql but its very similar to sql so my problem is that i dont know how to check if those two numbers are equal, so if they are equal then there are no gaps in column numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 07:44:41

quote:
Originally posted by darius12

SELECT count(number) AS d1, MAX(number + 1) as d2 FROM diplomas WHERE d2.number = d1.number

This is code in postgresql but its very similar to sql so my problem is that i dont know how to check if those two numbers are equal, so if they are equal then there are no gaps in column numbers.

i dont think this query will server you what you want. you're never checking presence of MAX(number + 1) in table at all. for that you need a self join with same table on number + 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darius12
Starting Member

16 Posts

Posted - 2011-12-05 : 08:59:31

quote:
Originally posted by visakh16

quote:
Originally posted by darius12

SELECT count(number) AS d1, MAX(number + 1) as d2 FROM diplomas WHERE d2.number = d1.number

This is code in postgresql but its very similar to sql so my problem is that i dont know how to check if those two numbers are equal, so if they are equal then there are no gaps in column numbers.

i dont think this query will server you what you want. you're never checking presence of MAX(number + 1) in table at all. for that you need a self join with same table on number + 1

This code works well without the WHERE part...Do you have any idea how do i make it work?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 11:57:36

[code]
SELECT case when count(d1.number)- count(d2.number) > 1 then 'gap present' else 'gap not present' end
FROM diplomas d1
LEFT JOIN diplomas d2
ON d2.number = d1.number+1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

consultor.dwh
Starting Member

6 Posts

Posted - 2011-12-05 : 17:04:02

We need to compare one record with the next, and check the code's difference. If difference is bigger than 1 we need to report the offending row. The query will be:

1st create table and some data
create table _deleteme (
cod int
)

insert into _deleteme (cod) values (1), (2), (3), (5), (7), (8), (10)

Then, find the gaps

with data (
cod
,rownum
) as (
select cod
,row_number() over (order by cod)
from _deleteme
)
select a.cod
,n.cod con_pre
,n.cod - a.cod gap
from data a
join data n
on a.rownum = n.rownum - 1
where (n.cod - a.cod) > 1

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-12-05 : 19:06:23

quote:
Originally posted by darius12

it doesnt need to find gaps, just send a single message at the end

if
(select top 1 prv."number",
from "numbers" prv
left join "numbers" nxt on nxt.no = prv.no+1
where nxt.no is null
order by 1)
<
(select max(no) from "numbers") then select 'gap'
else select 'no gap'

Assuming there is an index on the no column, this solution is the fastest I found.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 23:51:59

quote:
Originally posted by consultor.dwh

We need to compare one record with the next, and check the code's difference. If difference is bigger than 1 we need to report the offending row. The query will be:

1st create table and some data
create table _deleteme (
cod int
)

insert into _deleteme (cod) values (1), (2), (3), (5), (7), (8), (10)

Then, find the gaps

with data (
cod
,rownum
) as (
select cod
,row_number() over (order by cod)
from _deleteme
)
select a.cod
,n.cod con_pre
,n.cod - a.cod gap
from data a
join data n
on a.rownum = n.rownum - 1
where (n.cod - a.cod) > 1

no need of checking each cases as per op
i think he's just looking at boolean result to indicate if gap exists or not in table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources