SQL Query - How-To Question? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query - How-To Question?

Author

Topic

wiseteufel
Starting Member

2 Posts

Posted - 2006-03-29 : 13:49:19

Hi All -

The following SQL statement "Select NAME,SIZE,PATH FROM jpgFiles" Yields:

dsc00852.jpg 555254 g:\search\20gb backup\alex back up\digital photos\originaldsc00852.jpg 555254 g:\search\backup work pc\alex back up\digital photos\original
dsc00852.jpg 55308 g:\grcs\
dsc00852.jpg 55308 g:\search\20gb backup\alex back up\desktop\clean desk 4\photo\
dsc00852.jpg 55308 g:\search\20gb backup\alex back up\digital photos\baby\
dsc00852.jpg 55308 g:\search\backup work pc\alex back up\desktop\clean desk4\photo\
dsc00852.jpg 55308 g:\search\backup work pc\alex back up\digital photos\baby
dsc00853.jpg 542186 g:\search\20gb backup\alex back up\digital photos\original photos
dsc00853.jpg 542186 g:\search\backup work pc\alex back up\digital photos\original photos
dsc00853.jpg 44315 g:\grcsdsc00853.jpg 44315 g:\search\20gb backup\alex back up\desktop\clean desk 4\photo
dsc00853.jpg 44315 g:\search\20gb backup\alex back up\digital photos\baby
dsc00853.jpg 44315 g:\search\backup work pc\alex back up\desktop\clean desk4\photo
dsc00853.jpg 44315 g:\search\backup work pc\alex back up\digital photos\baby
.
.
.
I need a query that will list only one occurence of the file Name, Size,Path
based on the LARGEST size of the multiple occurances of the NAME. i.e. NAME =
dsc00853.jpg has SEVEN occurances with different sizes and Paths. So I need
Only the first line in the above list related to dsc00853.jpg:
"dsc00853.jpg 542186 g:\search\20gb backup\alex back up\digital photos\original photos\";
as well the
first line of the list related to dsc008532jpg:
"dsc00852.jpg 555254 g:\search\20gb backup\alex back up\digital photos\original\".

Please help. Thanks.

Wise Teufel.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-30 : 10:46:39

select t1.*
from tbl t1
where not exists
(
select * from tbl t2
where t1.name = t2.name
and (t1.size < t2.size
or (t1.size = t2.size and len(t1.name) < len(t2.name)))
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wiseteufel
Starting Member

2 Posts

Posted - 2006-03-30 : 16:08:39

Here is what I finally did. Found another piece of code off internet:

I added an Identity Column called idNo to the table called AlexP;

SELECT idNo,NAME, SIZE, PATH INTO #tmp1 FROM AlexP ORDER BY NAME, SIZE, idNo
--SELECT * from #tmp1 -- drop table #tmp1 drop table #tmp2

select t1.* INTO #tmp2
from #tmp1 t1
where not exists
(
select * from #tmp1 t2
where t1.name = t2.name
and (t1.size < t2.size
or (t1.size = t2.size and len(t1.name) < len(t2.name)))
)
SELECT * FROM #tmp2

PRINT 'Duplicates in Table'
SELECT * FROM #tmp2
WHERE idNo IN
(SELECT B.idNo
FROM #tmp2 A JOIN #tmp2 B
ON A.idNo <> B.idNo
AND A.size = B.size
AND A.name = B.name)

PRINT 'Duplicates to Delete'
SELECT * FROM #tmp2
WHERE idNo IN
(SELECT B.idNo
FROM #tmp2 A JOIN #tmp2 B
ON A.idNo < B.idNo -- < this time, not <>
AND A.size = B.size
AND A.name = B.name)

PRINT 'Delete Duplicates'
DELETE FROM #tmp2
WHERE idNo IN
(SELECT B.idNo
FROM #tmp2 A JOIN #tmp2 B
ON A.idNo < B.idNo -- < this time, not <>
AND A.size = B.size
AND A.name = B.name)

PRINT 'Cleaned-up Table'
SELECT * FROM #tmp2

quote:
Originally posted by nr

select t1.*
from tbl t1
where not exists
(
select * from tbl t2
where t1.name = t2.name
and (t1.size < t2.size
or (t1.size = t2.size and len(t1.name) < len(t2.name)))
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources