difficult sql query - 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

Other Forums

MS Access

difficult sql query

Author

Topic

houssem
Starting Member

3 Posts

Posted - 2012-02-22 : 08:40:14

I have a table containing many columns, I have to make my selection according to these two columns:
TIME | ID
-216 |AZA
215 | AZA
56 | EA
-55 | EA
66 | EA
-03 | AR
03 | OUI
-999 | OP
999 | OP
04 | AR
87 | AR

The expected output is

TIME | ID
66 | EA
03 | OUI
87 | AR

I need to select the rows with no matches. There are rows which have the same ID, and almost the same time but inversed with a little difference. For example the first row with the TIME -216 matches the second record with time 215. I tried to solve it in many ways, but everytime I find myself lost.

houssem
Starting Member

3 Posts

Posted - 2012-02-23 : 04:29:29

I tried to solve it by this one :

SELECT t1.TIME,MAX(t2.TIME),t1.ID FROM mytable t1 JOIN mytable t2 ON
t1.ID = t2.ID AND t1.TIME > t2.TIME WHERE ABS(t1.TIME + t2.TIME) < 3 GROUP BY
t1.TIME,t1.ID;

I want to drop this result and obtain the remaining rows, how do I proceed?

Csoft
Starting Member

8 Posts

Posted - 2012-02-23 : 04:47:16

Check out this...

SELECT * FROM mytable WHERE id NOT IN (

SELECT id FROM mytable GROUP BY id HAVING COUNT(id)>1)

houssem
Starting Member

3 Posts

Posted - 2012-02-23 : 05:11:41

@Csoft: Thank you, but I think that you don't understand me oO.I need to select the rows with no matches.I want to drop rows which have the same ID, and almost the same time but inversed with a little difference (Each pair)
)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 05:12:56

wouldn't work -- he's not looking for exact duplicates. he's looking for things that don't have a inverse in some range. interesting.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 05:30:01

This will probably work:


DECLARE @sample TABLE (
	[TIME] INT
	, [ID] VARCHAR(5)
	)
INSERT @sample
VALUES
	  (-216, 'AZA')
	, (215,'AZA')
	, (56, 'EA')
	, (-55, 'EA')
	, (66, 'EA')
	, (-03, 'AR')
	, (03, 'AR')
	, (04, 'AR')
	, (87, 'AR')
	, (03, 'OUI')
	, (-999, 'OP')
	, (999, 'OP')

/*
The expected output is

TIME | ID
66 | EA
03 | OUI
87 | AR
*/

DECLARE @range INT = 1

SELECT * FROM @sample

; WITH uqSample AS (
	SELECT ROW_NUMBER() OVER ( ORDER BY [ID] ) AS [uk]
	     , [TIME]
	     , [ID]
	FROM @sample
	)
SELECT s1.[TIME], s1.[ID]
FROM uqSample AS s1
WHERE
	NOT EXISTS (
		SELECT 1
		FROM uqSample AS s2
		WHERE s2.[ID] = s1.[ID]
		  AND s2.[uk] <> s1.[uk]
		  AND ABS(ABS(s2.[TIME]) - ABS(s1.[TIME])) <= @range
		  )

But it will be slow.

There is no unique key on the table so I made one (ROW_NUMBER()).

You can tune the query by adjusting the @range variable.

This will table scan. You can add some indexes to make it marginally faster (on the ID field) but it'll still be slow.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-23 : 06:12:32

Actually - I have no idea if that sql will work in access and I have no way of testing it :(

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources