Selecting semi-dupliactes - 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

General SQL Server Forums

New to SQL Server Programming

Selecting semi-dupliactes

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-06-30 : 04:07:21

I'm sure the answer to this is simple, but it's first thing on Monday and my brain isn't working.

Here's some sample data:


LineageAdStatsID adID        clicks      sectorPage  
---------------- ----------- ----------- ----------- 
4647             10739       1           0
4646             2143        1           0
4645             10739       1          617

You'll note that there are two rows above with the same AdId and different sectorpage values. It's possible for an AdId to have three or more rows in the table. What I need to do is select all the adIds from this table which have a row in the table of sectorpage = 0 AND at least one value where sectorpage > 0

Anyone help me out?

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:21:47

SELECT adID
FROM YourTable
GROUP BY adID
HAVING SUM(CASE WHEN MIN(sectorPage)=0 AND MIN(sectorPage)<>MAX(sectorPage) THEN 1 ELSE 0 END)>0

mattt
Posting Yak Master

194 Posts

Posted - 2008-06-30 : 04:44:44

Hi,

Thanks for helping. I'm afraid I can't claim to entirely understand what this query is trying to do, but running it gives me ...

Server: Msg 130, Level 15, State 1, Line 4
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

quote:
Originally posted by visakh16

SELECT adID
FROM YourTable
GROUP BY adID
HAVING SUM(CASE WHEN MIN(sectorPage)=0 AND MIN(sectorPage)<>MAX(sectorPage) THEN 1 ELSE 0 END)>0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 04:52:58

[code]SELECT adID
FROM YourTable
GROUP BY adID
HAVING COUNT(DISTINCT sectorPage) >=2
AND
SUM(CASE WHEN sectorPage=0 THEN 1 ELSE 0 END)>0[/code]

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-06-30 : 04:54:18

DECLARE @Table TABLE (LineageAdStatsID INT, adID INT, clicks INT,sectorPage INT)

INSERT INTO @Table
SELECT 4647, 10739, 1, 0 UNION ALL
SELECT 4646, 2143, 1, 0 UNION ALL
SELECT 4645, 10739, 1, 617

--SELECT * FROM @Table

SELECT T1.*
FROM @Table T1
INNER JOIN @Table T2 ON T1.adID = T2.adID
AND T2.sectorPage > 0
WHERE T1.sectorPage = 0

mattt
Posting Yak Master

194 Posts

Posted - 2008-06-30 : 04:55:18

That's great thanks. And by comparing the two I get a good lesson in using CASE as well :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources