CODEWITHSUNDEEP
sqlsql-servert-sql
JOE SKEET
JOE SKEET

Reputation: 8118

help with deleting values with difficult t-sql query

select * from qvalues where rowid in (

select rowid  from qvalues where rowid in (select rowid from batchinfo where datapath not like  '%thc%' and datapath not like '%pain%' and datapath not like 'tf1' and datapath like '%excel short%') 
and (compound='etg')
and name='qc1'
group by rowid
having (COUNT(rowid)>1))
and name='qc1'
order by rowid,rid

the above statement returns this:

rid name    compound    rt  response    finalConc   qvalue  rowid
508898  QC1 ETG 33,463.34   3,388.83    719.38  100 17800
508900  QC1 ETG 33,463.34   3,388.83    617.09  100 17800
510382  QC1 ETG 41,657.33   4,662.37    742.01  100 17860
510384  QC1 ETG 33,463.34   3,388.83    617.09  100 17860
527192  QC1 ETG 106,578.46  15,116.28   894.79  100 18478
527194  QC1 ETG 33,463.34   3,388.83    617.09  100 18478
527198  QC1 ETG 110,313.48  16,699.65   877.02  100 18479
527200  QC1 ETG 33,463.34   3,388.83    617.09  100 18479
527204  QC1 ETG 107,128.41  15,052.29   852.51  100 18480
527206  QC1 ETG 33,463.34   3,388.83    617.09  100 18480
527210  QC1 ETG 116,257.86  16,929.16   841.9   100 18481
527212  QC1 ETG 33,463.34   3,388.83    617.09  100 18481
527216  QC1 ETG 102,569.68  17,601.24   991.17  100 18482
527218  QC1 ETG 33,463.34   3,388.83    617.09  100 18482
527222  QC1 ETG 120,925.71  15,204.38   715.48  100 18483
527224  QC1 ETG 33,463.34   3,388.83    617.09  100 18483
529873  QC1 ETG 213,327.65  26,499.14   691.04  100 18576
529875  QC1 ETG 33,463.34   3,388.83    617.09  100 18576
540030  QC1 ETG 69,627.37   9,401.47    814.5   100 18987
540032  QC1 ETG 33,463.34   3,388.83    617.09  100 18987
540182  QC1 ETG 56,768.32   7,682.65    921.53  100 18993
540184  QC1 ETG 33,463.34   3,388.83    617.09  100 18993
540186  QC1 ETS 92,853.28   2,428.01    14.06   100 18994

i would like to only keep the rows having the first unique ROWID and delete the rest. how do i delete every record after the first occurrence of a unique ROWID??

Upvotes: 0

Views: 141

Answers (3)

LaGrandMere
LaGrandMere

Reputation: 10359

WITH cte AS 
(
select rowid  from qvalues where rowid in (select rowid from batchinfo where datapath not like  '%thc%' and datapath not like '%pain%' and datapath not like 'tf1' and datapath like '%excel short%') 
and (compound='etg')
and name='qc1'
group by rowid
having (COUNT(rowid)>1)
)
SELECT qvalues.* FROM qvalues INNER JOIN cte ON cte.rowid = qvalues.rowid
WHERE qvalues.rid = (SELECT MIN(rid) from cte where cte.rowid = qvalues.rowid)
ORDER BY rowid

This should do the job :) You use a Common Table Expression, and once it's done, you get the lines with MIN(rid).

Upvotes: 0

chezy525
chezy525

Reputation: 4174

I assume you mean first as in, "has the lowest rid", in which case the following should work.

;with cte as (
  --put your query here
)
select c1.*
from cte c1
  join (
    select rowID, min(rid) minRID
    from cte
    group by rowID
  ) c2 on c1.rowID=c2.rowID
      and c1.rid = c2.minRID

That said, my gut reaction is that you're initial query can be optimized somewhat, making everything simpler. However, I can't know that without the schema... so maybe not...

Upvotes: 0

bobs
bobs

Reputation: 22204

This may help.

WITH cte AS
   (
   SELECT rowid,
      ROW_NUMBER() OVER (PARTITION BY rowid ORDER BY rid) AS sequence
   FROM qvalues
   WHERE rowid IN
        (SELECT rowid
        FROM batchinfo
        WHERE datapath NOT LIKE '%thc%'
            AND datapath NOT LIKE '%pain%'
            AND datapath NOT LIKE 'tf1'
            AND datapath LIKE '%excel short%'
        )
        AND (compound='etg')
        AND name='qc1'
   GROUP BY rowid
   HAVING COUNT(rowid)>1
   )

DELETE
FROM cte
WHERE sequence > 1

The query uses a common table expression (CTE) to get the rows and it adds a sequence number for each rowid group. The sequence is determined by ordering rid. So, the first rowid has the smallest rid.

The delete statement removes all rows that are not the first row in the group.

Upvotes: 1

Related Questions