[SQL] ontdubbelen op basis van datum

[mazzl]

Hoi Tweakers,
ik heb een vrij simpel query vraagje denk ik, maar ik loop vast.
het gaat om ms-sql 2005.

ik heb een tabel

SQL:

CREATE TABLE [dbo].[TabelNaam](
    [Identity] [bigint] NULL,
    [RowID] [varchar](80) NULL,
    [Time] [datetime] NULL,
    [Status] [varchar](50) NULL
) ON [PRIMARY]

nu heb ik in het RowID veld een aantal dubbele waardes, maar deze hebben wel een unieke identity en Time.
ik wil alleen de recods deleten waar:
het RowID niet uniek is, en het Time veld niet de jongste waarde is, en de waarde in het veld [status] <> 'T'


ik kan de dubbele records vinden:

SQL:

SELECT RowID,
 COUNT(RowID) AS NumOccurrences
FROM TableName GROUP BY RowID
HAVING ( COUNT(RowID) > 1 )

en ik heb dit nog bedacht....

SQL:

select mindates.*
from (select rowid, min(time) as time from TableName
group by rowid) as mindates
inner join TableName
on mindates.rowid = TableNames.rowid
and TableName.[status] <>'T'

maar ik loop vast.

ik zal in het Nederlands mijn query nog even verwoorden.

ik wil graag verwijderen uit een tabel, alleen de records waarvan er dubbele waarden in veld x voorkomen, en die niet de jongste waarde uit het datum veld hebben, en in veld y niet de waarde T hebben staan.

dus ik wil niet verwijderen, de records met een unieke waarde in veld x, en van de dubbele records niet de records met de jongste waarde in het datum veld

als iemand mij hierbij kan helpen ben ik heel blij

[Zoijar]

Even uit,'n hoofd, werkt dit? Ik zou het eerst even testen met een select ipv delete.... (weet niet of het werkt, en ook niet of dit nou de beste manier is... et lijkt me omslachtig hehe)

SQL:

WITH Excluded AS (
   SELECT RowID, COUNT(RowID) AS cnt, MAX(Time) AS mtime FROM TableName GROUP BY RowID HAVING(cnt > 1)
)
SELECT Identity FROM TableName, Excluded
WERE TableName.RowID = Excluded.RowID AND TableName.Time != Excluded.mtime

[ Voor 13% gewijzigd door Zoijar op 26-01-2012 10:44 ]

[mazzl]

Zoijar schreef op donderdag 26 januari 2012 @ 10:41:
Even uit,'n hoofd, werkt dit? Ik zou het eerst even testen met een select ipv delete.... (weet niet of het werkt, en ook niet of dit nou de beste manier is... et lijkt me omslachtig hehe)

SQL:

WITH Excluded AS (
   SELECT RowID, COUNT(RowID) AS cnt, MAX(Time) AS mtime FROM TableName GROUP BY RowID HAVING(cnt > 1)
)
SELECT Identity FROM TableName, Excluded
WERE TableName.RowID = Excluded.RowID AND TableName.Time != Excluded.mtime

Thanx voor je reactie!
komen al een heel eind.

SQL:

WITH Excluded AS (
   SELECT RowID, COUNT(RowID) AS cnt, MAX(Time) AS mtime FROM TableName GROUP BY RowID HAVING(cnt > 1)
)
SELECT Identity FROM TableName, Excluded
WHERE TableName.RowID = Excluded.RowID AND TableName.Time != Excluded.mtime

een h toegevoegd in de where clause maar krijg nu de melding.

"Msg 207, Level 16, State 1, Line 2
Invalid column name 'cnt'."

het lijk er op dat sql bij het "cnt>1" niet de waarde kan gebruiken die net daarvoor gedeclareerd is.

[Zoijar]

oh, nouja, dan zet je toch weer COUNT(RowID) neer? (Ik heb het niet getest).

(als je wilt deleten kan je om deze hele query gewoon een 'DELETE FROM TableName WERE Identity IN (<query boven>)" zetten trouwens. Maar 3 queries lijkt me overdreven omslachtig... aan de andere kant, als het werkt en snel genoeg draait...waarom niet)

[ Voor 61% gewijzigd door Zoijar op 26-01-2012 11:15 ]

[Redshark]

Zoijar schreef op donderdag 26 januari 2012 @ 10:41:
Even uit,'n hoofd, werkt dit? Ik zou het eerst even testen met een select ipv delete.... (weet niet of het werkt, en ook niet of dit nou de beste manier is... et lijkt me omslachtig hehe)

SQL:

WITH Excluded AS (
   SELECT RowID, COUNT(RowID) AS cnt, MAX(Time) AS mtime FROM TableName GROUP BY RowID HAVING(cnt > 1)
)
SELECT Identity FROM TableName, Excluded
WERE TableName.RowID = Excluded.RowID AND TableName.Time != Excluded.mtime

die max(time) moest toch verwijderd worden of begrijp ik het nu niet?

Hier ook een poging, is wel een tijdje geleden dus ik zou hem ook eerst testen met een select ;-)

SQL:

delete t1
from
Tablename t1
inner join 
(select
  t2.RowID
, count(t2.RowID) as NumOccurrences
, min(t2.Time) as timestamp
, min(t2.Identity) as Identity
from 
    TableName t2
where
    t.[status] <>'T'
group by 
    RowID
having
 count(RowID) > 1) bewaren
on t1.rowID = bewaren.rowID
where 
t1.time <> bewaren.NumOccurrences
and t1.identity <> bewaren.Identity

[Zoijar]

Redshark schreef op donderdag 26 januari 2012 @ 12:01:
die max(time) moest toch verwijderd worden of begrijp ik het nu niet?

Volgens mij bedoelde hij alles dat dubbel is in rowid verwijderen behalve het (tijd) maximum.

[mazzl]

Zoijar schreef op donderdag 26 januari 2012 @ 12:21:
[...]

Volgens mij bedoelde hij alles dat dubbel is in rowid verwijderen behalve het (tijd) maximum.

idd.
de niet dubbele records wil ik behouden, en van de dubbele, alleen het record met de jongste datum behouden.
ga nu even de andere input testen

[RobIII]

Even tussen haakjes; als je code/queries post (zie topicstart), gebruik dan code tags a.u.b.

[Redshark]

mazzl schreef op donderdag 26 januari 2012 @ 12:33:
[...]


idd.
de niet dubbele records wil ik behouden, en van de dubbele, alleen het record met de jongste datum behouden.
ga nu even de andere input testen

Aha, te snel gelezen. De 'jongste' geinterpreteerd als 'eerste'. In dat geval in mijn code even wat aanpassen van

SQL:

, min(t2.Time) as timestamp

naar

SQL:

, max(t2.Time) as timestamp

Mocht mijn code het verder werken ;-)

[mazzl]

Hoi Redshark,
ik ben nog even aan het puzzelen, maar

SQL:

t1.time <> bewaren.NumOccurrences 

hier vergelijk je twee verschillende colommen met elkaar toch?

als ik

SQL:

SELECT RowID,
 COUNT(RowID) AS NumOccurrences
FROM TableName GROUP BY RowID
HAVING ( COUNT(RowID) > 1 )
order by NumOccurrences

krijg ik bij een aantal records +20 occurances, bij jullie queries komen deze niet boven de 4 uit. ik neem dus bepaalde records niet mee.. geen idee waarom.
we zijn wel in de buurt lijkt het zo.

thanx so far!

ps. de sql code tag doet het niet ... DO'H foreward sl.... //// gebruiken!

[ Voor 8% gewijzigd door mazzl op 26-01-2012 13:45 ]

[Redshark]

Koffietekort... het gaat erom dat de tijden moeten verschillen idd.

SQL:

t1.time <> bewaren.timestamp 

ff wat zwart goud scoren hier...

[Rotterdammertje]

Dit kan ook heel elegant met de analytic functie "ROW_NUMBER". Uit het blote hoofd:

SQL:

SELECT [Identity], [Status]
FROM
(
  SELECT
    [Identity], [Status]
    ROW_NUMBER() OVER (PARTITION BY [RowID] ORDER BY [Time] DESC) AS RecNumber
    FROM [TableName]
)
WHERE
  RecNumber > 1 AND [Status] <> 'T';

Dit nummert de records met gelijke RowID aflopend gesorteerd op Time; dus per RowID krijgt het record met de grootste tijd een RecNumber van 1, de daarop volgende RecNumber 2, etc. Vervang SELECT door DELETE en je hebt wat je wil.

[mazzl]

Hoi Rotterdammetrje, bedankt voor je reactie,
ik ga zelf ook nog even googelen op je constructie maar op dit moment krijg ik als reactie.

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.

straks meer,

ps. kan dit ook in sql 2005 ?

SQL:

SELECT [RowID], [Status] 
FROM 
( 
  SELECT 
    [RowID], [Status], 
    ROW_NUMBER() OVER (PARTITION BY [I3_RowID] ORDER BY [Time] DESC) AS RecNumber 
    FROM TableName
) 
Where 
  RecNumber > 1 AND [Status] <> 'T';

comma toegevoegd achter, status, nu komt de query al iets verder, krijg melding.

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Where'.

[ Voor 50% gewijzigd door mazzl op 27-01-2012 11:50 ]

[Rotterdammertje]

Ah ja, in T-SQL moet de subquery een table-alias krijgen..

SQL:

SELECT [RowID], [Status] 
FROM 
( 
  SELECT 
    [RowID], [Status], 
    ROW_NUMBER() OVER (PARTITION BY [RowID] ORDER BY [Time] DESC) AS RecNumber 
    FROM TableName
) AS NumberedRecs
Where 
  RecNumber > 1 AND [Status] <> 'T';

[mazzl]

pff.. ben er bijna, de query is nu goed
alleen nu nog de delete ervan maken.

SQL:

delete where rowid in
select [rowid],[status]
FROM 
(  
  SELECT  
    [RowID], [Status], time, 
    ROW_NUMBER() OVER (PARTITION BY [RowID] ORDER BY time DESC) AS RecNumber  
    FROM TableName 
) AS NumberedRecs 
Where  
  RecNumber > 1 AND [Status] <> 'T';

krijg foutmelding

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'where'.

sql ... niet mijn sterkste punt.
thankx voor de hulp zo ver!

[RobIII]

SQL:

delete where rowid in 
select [rowid],[status] 

Dat gaat sowieso niet werken; waarvoor select je status? Stel je even voor wat er gebeurt; je resultset bevat 2 velden; welke moet de in-clause dan gebruiken?

Verder moeten er volgens mij haakjes om de hele select.

[mazzl]

@robill.

hoi, daar heb je op zich wel gelijk in, probeer het nu zo

SQL:

delete from TableName where  [rowid],[status] exists in 
select( [rowid],[status]
FROM 
(  
  SELECT  
    [RowID], [Status], time, 
    ROW_NUMBER() OVER (PARTITION BY [RowID] ORDER BY time DESC) AS RecNumber  
    FROM NL_TableName 
) AS NumberedRecs 
Where  
  RecNumber > 1 AND [Status] <> 'T');

maar krijg dan weer een heleboel andere foutmeldingen.

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near ','.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

ga nu even tien minuten pauze houden, zie niets meer

[Zoijar]

Wat was er eigenlijk mis met mijn query? Deed die het niet? Ik had het idee dat dat gewoon moest werken.

[CaVeFiSh]

FF uit me hoofd een verbetering:

code:

DELETE FROM TableName 
where rowid in (
select [rowid]
FROM 
(  
  SELECT  
    [RowID], [Status], time, 
    ROW_NUMBER() OVER (PARTITION BY [RowID] ORDER BY time DESC) AS RecNumber  
    FROM TableName 
) AS NumberedRecs 
Where  
  RecNumber > 1 AND [Status] <> 'T' )

[mazzl]

Hoi CaveFish,
zo werkt die wel

dank aan iedereen voor de hulp!