A complicated query (for me)

Question

A complicated query (for me)

vasimihalca

Grasshopper

Points: 14
More actions
March 8, 2009 at 5:50 am

#128791

Hello !!
This is my first post on this forum, and I have a little problem at work that I don`t know how to resolve it.
I have experience on writing simple queries but this one is a real headache.
So I have two tables
syncml_devices
device_id, device_identifier, user_id .....
------------------------------------------------
56787 xxxxx 500
76807 xxxxx 500
56788 xxxxx 500
as you can notice for some stupid reason, there are multiple records with the same device_indentifier and user_id
Normally it should be just a device_id for each pair of (device_identifier, user_id), so i have to fix this situation.
This table is linked to another one
syncml_anchors
anchor_id device_id time_key
------------------------------------------------
56652 56787 10.10.2008
26547 76807 11.10.2008
58799 56788 12.10.2008
So, my task is that from all devices with the same (device_identifier, user_id) I have to keep only those with bigger time_key.
The rest of devices and anchors associated to them, that do not match this condition should be deleted.
Can you please help me with a snippet of sql that can do that ?! 🙂
Thx

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

The (non-)uniqueness is a commo problem. Maybe after fixing the problem you should think about an unique index on device table identifier/user_id.

Your current case:

* First you have to identify the data which have not to be deleted

* Now you can use a join to nuke the evil entries 🙂

[font="Courier New"]

DECLARE @devices TABLE (id INT, identifier VARCHAR(100), USER_ID INT)

DECLARE @anchors TABLE (id INT, device_id INT, time_key DATETIME)

INSERT INTO @devices VALUES (56787, 'abc', 500)

INSERT INTO @devices VALUES (76807, 'def', 500)

INSERT INTO @devices VALUES (56788, 'abc', 500)

INSERT INTO @anchors VALUES (56652, 56787, '20081010')

INSERT INTO @anchors VALUES (26547, 76807, '20081011')

INSERT INTO @anchors VALUES (58799, 56788, '20091012')

--DELETE dev

-- FROM @devices dev

-- JOIN

DELETE dev

FROM @devices dev

JOIN @anchors anc ON dev.id = anc.device_id

JOIN (SELECT dev.identifier, dev.USER_ID, MAX(anc.time_key) time_key

FROM @devices dev

JOIN @anchors anc ON dev.id = anc.device_id

GROUP BY dev.identifier, dev.USER_ID) dev2

ON dev.identifier = dev2.identifier AND dev.USER_ID = dev2.USER_ID AND anc.time_key != dev2.time_key

SELECT * FROM @devices[/font]

BTW

For next posts you shold not use the german date format (I'm from germany and can read it, but I don't think everybody can 😉 ). Maybe use "2009-03-08" (odbc/ansi), "03/08/2009" (us) or "20090308" (SQL Server).

Greets

Flo

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

vasimihalca (3/8/2009)
Hello !!
This is my first post on this forum, and I have a little problem at work that I don`t know how to resolve it.
I have experience on writing simple queries but this one is a real headache.
So I have two tables
syncml_devices
device_id, device_identifier, user_id .....
------------------------------------------------
56787 xxxxx 500
76807 xxxxx 500
56788 xxxxx 500
as you can notice for some stupid reason, there are multiple records with the same device_indentifier and user_id
Normally it should be just a device_id for each pair of (device_identifier, user_id), so i have to fix this situation.
This table is linked to another one
syncml_anchors
anchor_id device_id time_key
------------------------------------------------
56652 56787 10.10.2008
26547 76807 11.10.2008
58799 56788 12.10.2008
So, my task is that from all devices with the same (device_identifier, user_id) I have to keep only those with bigger time_key.
The rest of devices and anchors associated to them, that do not match this condition should be deleted.
Can you please help me with a snippet of sql that can do that ?! 🙂
Thx

Just to be absolutely clear... you're not actually saying you want to DELETE the duplicate entries, are you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vasimihalca Grasshopper Points: 14 More actions · Answer 3

Hy

Thx for your help, you have open my mind 🙂

About using constraints, all I can say is that I`m a developer working on a project that is already implemented, I have made that suggestion but it has no result yet.

Thx again !!

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi Jeff

He said:

The rest of devices and anchors associated to them, that do not match this condition should be deleted.

I just did not read the part with the anchors 🙂

Greets

Flo

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

I agree, Flo... I just want to make sure what the OP means by "Deleted"... sometimes folks just mean "not included in the result set" and I wanted to be absolutely sure the OP understood that your good code was going to actually delete rows from a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Hi Jeff

Sorry for the stupid question... What means "OP" ? Sometimes I hate all these acronyms :w00t: ...

Thank you

Flo

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Yow... sorry Flo... "OP" can mean either "the Original Poster" or the "Original Post" depending on how it's used in a sentance. And, it can also be a bit ambiguous as to which meaning is implied if the sentance isn't written quite right.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Thank you for translation! In my company the "OP" is the "operational process" but I've been quiet sure that you have been talking about something else ;).

Greets

Flo