How to write a query to find 3 sequential items ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to write a query to find 3 sequential items ?

Author

Topic

laro
Starting Member

3 Posts

Posted - 2010-05-13 : 04:20:59

Hi,

I use MySql,

I have a table (products) with the following fields (number (primary key), name)
The table order by the number field

for example:

number | Name
-------------
1 | aa
2 | bb
3 |
4 |
5 | ee
6 | ff
7 | gg
8 |
9 |
10 |
11 |
12 | ll
13 | mm

I need to write a query
which need to find 3 numbers
which haven't have a name
and theier number are sequently

for exmple:
in the above example
the answer will be:

8,
9,
10

I use MYSQL,
and I dont know how to right the above query

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-13 : 05:44:36

This site is for MS SQL Server
Post your question at www.mysql.com

Madhivanan

Failing to plan is Planning to fail

laro
Starting Member

3 Posts

Posted - 2010-05-13 : 05:52:05

Ok,

So how can I write the query with MS SQL Server ?

Sachin.Nand

2937 Posts

Posted - 2010-05-13 : 06:14:08

quote:
Originally posted by laro

Ok,

So how can I write the query with MS SQL Server ?

Kind of in hurry.I have written a query that will give the min number & max number matching your criteria.You will just have to find all the numbers between these two numbers.


declare @tbl as table(number int,name varchar(40))
insert into @tbl
select 1,'aa' union all
select 2,'bb' union all
select 8,'' union all
select 9,'' union all
select 10,'' union all
select 11,'ll' union all
select 12,'mm' union all
select 13,'ll' union all
select 14,'' 

select * from @tbl

select MIN(number)AS MINNUMBER,MAX(number)AS MAXNUMBER from
(
	select *,number-ROW_NUMBER()over(partition by name order by name) as rid from @tbl where name=''

)t group by rid having MAX(number)-MIN(number)=2

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources