Occurances count - 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)

Occurances count

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2003-02-11 : 13:27:46

I have the following recordset:

dir filn
5012759_c_04.Z 5004528.asm.4
5012759_c_04.Z 5006665.asm.4
5012763_b_04.Z 5004528.asm.4

I want to select only records that have only one record like '%.asm.%' in the filn column. So I would get:

dir filn
5012763_b_04.Z 5004528.asm.4

Thanks in Advance!
Lane

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-11 : 13:35:44

SELECT filn, count(*)
FROM Table1
WHERE filn LIKE '%.asm.%'
GROUP BY filn
HAVING COUNT(*) = 1

Edited by - tduggan on 02/11/2003 13:36:05

lane0618
Posting Yak Master

134 Posts

Posted - 2003-02-11 : 14:01:42

Doesn't seem to be working for me, I used:

SELECT files, count(*)
FROM z_lib
WHERE files LIKE '%.asm.%'
and (dir like '5012763%' or
dir like '5012759%')
GROUP BY files
HAVING COUNT(*) = 1

and got:

files
5006665.asm.4

I also tried grouping by the dir column and all record where returned. What am I doing wrong? BTW - there are other records that may use any of the records in files, so I do need to group by dir if possible.

Thanks,
Lane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources