Delete Query for older files for existing company

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

Delete Query for older files for existing company

Author

Topic

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-11 : 07:42:54

Hey,

Lets say I have a tblFiles
with the following columns
companyId FileType fileDate
companyId FileType fileDate
And my data could look something like
companyId FileType fileDate
4 3 11/03/2009
5 3 11/03/2009
4 3 09/03/2009
6 3 08/03/2009

What I would like is a delete script to delete records for existing companies i.e if there is more than 1 to delete the older dates ones but if there is only one then leave it be. So for example after i run the script my table would look like.

4 3 11/03/2009
5 3 11/03/2009
6 3 08/03/2009

Any Ideas ?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:46:31

[code]DELETE f
FROM tblFiles AS f
INNER JOIN (
SELECT CompanyID,
MAX(FileDate) AS FileDate
FROM tblFiles
GROUP BY CompanyID
) AS x ON x.CompanyID = f.CompanyID
WHERE f.FileDate < x.FileDate[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-11 : 07:46:48

delete t from
(select row_number()over(partition by companyid order by companyid) as rid ,* from Tablename)t where t.rid = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:48:50

quote:
Originally posted by bklr

delete t from
(select row_number()over(partition by companyid order by companyid) as rid ,* from Tablename)t where t.rid = 1

delete t from
(select row_number()over(partition by companyid order by filedate desc) as rid from Tablename) t
where t.rid > 1

E 12°55'05.63"
N 56°04'39.26"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-11 : 08:00:03

Hey Peso I was trying

DELETE f
FROM tblFiles AS f INNER JOIN
(SELECT intermediaryCode, MAX(completionReportDate) AS completionReportDate
FROM tblFiles
GROUP BY intermediaryCode) AS x ON x.intermediaryCode = f.intermediaryCode AND f.completionReportDate < x.completionReportDate AND
f.fileTypeId = 3

But I keep getting the error that f is an invalid object

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 08:22:25

Works for me

DELETE		f
FROM		tblFiles AS f
INNER JOIN	(
			SELECT		intermediaryCode,
					MAX(completionReportDate) AS completionReportDate
			FROM		tblFiles
			GROUP BY	intermediaryCode
		) AS x ON x.intermediaryCode = f.intermediaryCode
WHERE		f.completionReportDate < x.completionReportDate
		AND f.fileTypeId = 3

However I am not convinced you are putting the fileTypeID check on right spot.

E 12°55'05.63"
N 56°04'39.26"

Velnias
Yak Posting Veteran

58 Posts

Posted - 2009-03-11 : 08:33:10

I tried again as typed above there keep getting Invalid Object Name 'f'

Im trying

DELETE t
FROM (SELECT row_number() OVER (partition BY intermediaryCode
ORDER BY dateAdded DESC) AS rid
FROM tblPDFFiles
WHERE fileTypeId = 3) t
WHERE t .rid > 1

atm seems to work well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 13:45:54

quote:
Originally posted by Velnias

I tried again as typed above there keep getting Invalid Object Name 'f'

Im trying

DELETE t
FROM (SELECT row_number() OVER (partition BY intermediaryCode
ORDER BY dateAdded DESC) AS rid
FROM tblPDFFiles
WHERE fileTypeId = 3) t
WHERE t .rid > 1

atm seems to work well

i cant see f in posted code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources