October 22, 2008 at 11:15 pm
Hi,
I have the below table structure:
RowNumber UserId UserNmae Date
1 1 Sam 2008-10-23
2 2 Mark 2008-10-23
3 3 Bill 2008-10-23
4 4 Bob 2008-10-23
5 5 Jones 2008-10-23
6 6 Sam 2008-10-23
Now using the Rownumber values i want select two rows. That is,
1. If i select the rownumber 3 then i should also get rownumber 4 and
if select the rownumber 5 then i should also get rownumber 6
2. If i select the rownumber 3 then i should also get rownumber 4
3. If i select the rownumber 6 then i should i not get any rows as result because after the row number 6 there is no record. so the result should also show 0 records.
October 22, 2008 at 11:21 pm
Lots of ways:
select * from yourtable
where RowNumber in (@YourSelectRowNumber,@YourSelectRowNumber+1)
select * from yourtable
where RowNumber = @YourSelectRowNumber or RowNumber = @YourSelectRowNumber + 1
select * from yourtable
where RowNumber >= @YourSelectRowNumber and RowNumber <= @YourSelectRowNumber + 1
October 22, 2008 at 11:25 pm
Arul Manoj (10/22/2008)
Hi,I have the below table structure:
RowNumber UserId UserNmae Date
1 1 Sam 2008-10-23
2 2 Mark 2008-10-23
3 3 Bill 2008-10-23
4 4 Bob 2008-10-23
5 5 Jones 2008-10-23
6 6 Sam 2008-10-23
Now using the Rownumber values i want select two rows. That is,
1. If i select the rownumber 3 then i should also get rownumber 4 and
if select the rownumber 5 then i should also get rownumber 6
2. If i select the rownumber 3 then i should also get rownumber 4
3. If i select the rownumber 6 then i should i not get any rows as result because after the row number 6 there is no record. so the result should also show 0 records.
Use this
select top 2 * from (Select * from TABLENAME where RowNumber = 3 order by Rownumber) a
this will result the row 3 and 4.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 11:40 pm
Arul Manoj (10/22/2008)
Hi,I have the below table structure:
RowNumber UserId UserNmae Date
1 1 Sam 2008-10-23
2 2 Mark 2008-10-23
3 3 Bill 2008-10-23
4 4 Bob 2008-10-23
5 5 Jones 2008-10-23
6 6 Sam 2008-10-23
Now using the Rownumber values i want select two rows. That is,
1. If i select the rownumber 3 then i should also get rownumber 4 and
if select the rownumber 5 then i should also get rownumber 6
2. If i select the rownumber 3 then i should also get rownumber 4
3. If i select the rownumber 6 then i should i not get any rows as result because after the row number 6 there is no record. so the result should also show 0 records.
Here is a small script that shows 2 ways of doing it. I used my own table, because generating a script for it was faster then generating a script for your table, but it is very easy to understand and change it it according to your table structure.
use tempdb
create table test (i int, v char(1))
go
insert into test (i, v)
select 1, 'a'
union select 2,'b'
union select 3, 'c'
union select 4, 'd'
union select 5, 'e'
union select 6, 'f'
go
declare @i int
set @i = 6
--First way
select t1.i, t1.v
from test t1 inner join test t2 on t1.i = t2.i-1
where t1.i = @i
union
select t2.i, t2.v
from test t1 inner join test t2 on t1.i = t2.i-1
where t1.i = @i
--second way
select test.*
from test
where (i = @i or i = @i+1) and exists (select * from test where i=@i)
and exists (select * from test where i=@i+1)
go
drop table test
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 22, 2008 at 11:48 pm
Hi,
Actually my table has below structure:
RowNumber UserId UserNmae TimeDifferenceInMinutes
1 1 Sam 15
2 2 Mark 6
3 2 Mark 25
4 3 Bob 20
5 3 Bob 10
6 3 Bob 4
Now using the Rownumber values i want select two rows. That is,
1. If i select the TimeDifferenceInMinutes =6 then i should get rownumber 2 and
rownumber 3
2. If i select the TimeDifferenceInMinutes =20 then i should get rownumber 4 and
rownumber 5
3. If i select the TimeDifferenceInMinutes =4 then i should get 0 records.
October 23, 2008 at 10:12 am
Arul Manoj (10/22/2008)
Hi,Actually my table has below structure:
RowNumber UserId UserNmae TimeDifferenceInMinutes
1 1 Sam 15
2 2 Mark 6
3 2 Mark 25
4 3 Bob 20
5 3 Bob 10
6 3 Bob 4
Now using the Rownumber values i want select two rows. That is,
1. If i select the TimeDifferenceInMinutes =6 then i should get rownumber 2 and
rownumber 3
2. If i select the TimeDifferenceInMinutes =20 then i should get rownumber 4 and
rownumber 5
3. If i select the TimeDifferenceInMinutes =4 then i should get 0 records.
I used most of the code Adi Cohn made... Though maybe a bit more convenient for your needs, and I added the last change from your question:
declare @RowNumber int
declare @TimeDiff int
set @TimeDiff = 10
set @RowNumber = (SELECT RowNumber FROM test WHERE TimeDifferenceInMinutes = @TimeDiff)
--second way
select test.*
from test
where (RowNumber IN (@RowNumber,@RowNumber+1)) and exists (select * from test where RowNumber=@RowNumber)
and exists (select * from test where RowNumber=@RowNumber+1)
I assume you're looking for the exact time difference or else it makes things a tad more complicated.
Ronald
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
October 23, 2008 at 2:59 pm
Arul Manoj (10/22/2008)
Hi,Actually my table has below structure:
RowNumber UserId UserNmae TimeDifferenceInMinutes
1 1 Sam 15
2 2 Mark 6
3 2 Mark 25
4 3 Bob 20
5 3 Bob 10
6 3 Bob 4
Now using the Rownumber values i want select two rows. That is,
1. If i select the TimeDifferenceInMinutes =6 then i should get rownumber 2 and
rownumber 3
2. If i select the TimeDifferenceInMinutes =20 then i should get rownumber 4 and
rownumber 5
3. If i select the TimeDifferenceInMinutes =4 then i should get 0 records.
Before writing a way of doing so, here are 2 small tips. First of all when you want help, please describe the problem. Don’t describe a different problem and then change your question because the answer was for the question that was posted and apparently had nothing to do with the real problem that you were trying to solve. The second tip is to write a small script that includes DDL and insert statement for test data (just like I wrote in my previous answer). This way anyone that will response will have a quick way to replicate what ever is needed to solve the problem and test the solution. Since you didn’t do it, my solution is not tested:
select t1.*, t2.*
from MyTable t1 inner join MyTable t2
on t1.RowNumber = t2.RowNumber - 1
where t1.TimeDifferenceInMinutes = @TimeDifferenceInMinutes
Notice that this query might bring you back more then 2 records. If you have more then one per of records that meet the criteria, it will send to the client all the pers of records that meet that criteria.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 23, 2008 at 8:43 pm
I absolutely agree... in fact, I agree so much, that I wrote an article about it.
Please see the link in my signature for how to properly pose a problem on any SQL forum. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply