how compare values of 2 different rows in same tbl

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

how compare values of 2 different rows in same tbl

Author

Topic

forevercalz
Starting Member

5 Posts

Posted - 2005-11-17 : 04:10:56

Hi

i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?

thanks in advance.! any help?

well i actually get the answers by doin this...
select order1 from datetable group by order1 having count(*) > 1

however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins
eg

id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 04:41:50

Try this

Select t1.id,t1.order1 from yourTable t1 inner join yourTable t2 on t1.id<t2.id
where t1.order1=T2.order1 and datediff(minute,t1.date1,t2.date1)<30

Madhivanan

Failing to plan is Planning to fail

forevercalz
Starting Member

5 Posts

Posted - 2005-11-17 : 05:08:00

the problem is i only have one table. The duplicated records are from one table only.how to inner join? inner the same table?

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-17 : 05:18:00

quote:
Originally posted by forevercalz

the problem is i only have one table. The duplicated records are from one table only.how to inner join? inner the same table?

Yes user the same table in both the places.

Surendra

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-17 : 05:31:04

[code]
Select t1.id,t1.order1
from datetable t1 inner join datetable t2
on t1.id < t2.id
where t1.order1 = t2.order1
and datediff(minute, t1.date1, t2.date1) < 30
[/code]

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 05:42:19

quote:
Originally posted by forevercalz

the problem is i only have one table. The duplicated records are from one table only.how to inner join? inner the same table?

In that query replace yourTable by the actual table name. You dont need to use two tables

Madhivanan

Failing to plan is Planning to fail

forevercalz
Starting Member

5 Posts

Posted - 2005-11-17 : 19:55:36

ok ...however now i need to remove the id..so i enter the following code:

Select t1.order1
from datetable t1 inner join datetable t2
where t1.order1 = t2.order1
and datediff(minute, t1.date1, t2.date1) < 30

and i have this error:
SQL command not properly ended

i need to get this output:
ORDER1 DATE1
---------- --------------
aaaaa 20051001 12:45
aaaaa 20051001 12:20

i doin it in oracle sql...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-17 : 23:21:48

>>i doin it in oracle sql...

This is SQL Server Forum. If you are using Oracle post your question at www.DBForums.com

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-18 : 00:29:03

Yes. Microsoft SQL Server Forum

[KH]

forevercalz
Starting Member

5 Posts

Posted - 2005-11-18 : 01:51:08

ok..thanks for you help !! you guys are friendly..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources