Logic to filter - 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 2005 Forums

Transact-SQL (2005)

Logic to filter

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-16 : 17:49:32

Hello:

I have TestONE table, I want to filter out Pvalue > 6, this CID will not be inserted into TestTWO Table.

TestONE


Name	CID	   Tvalue	Pvalue
Jen	12121	    1	        1	
Jen	12121	    2	        2	
Jen	12121	    3	        3	
Jen	12121	    4	        4	


BEN	23231	    1	        1	
BEN	23231	    2	        2	
BEN	23231	    3	        3	
BEN	23231	    4	        5

Here is my code.

DECLARE @CID varchar(50)
DECLARE @Largest_pvalue int
DECLARE @MaxCID varchar(50)


select @Largest_pvalue = max(Pvalue), @CID = CID from TestONE group by CID


IF @Largest_pvalue > 4
	BEGIN
		PRINT "ERROR MESSAGE, @CID can not be inserted"
		set @MaxCID = @CID
		
	END
	else
		begin
			insert into TestTWO select * from TestONE where CID <> @MaxCID
		end

However, CID = 12121 sould be inserted into TestTWO, but none of them insert to table: TestTWO

Your help is always highly appreciate.

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-16 : 17:50:51

Sorry for the type. It should filter out Pvalue > 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 20:51:30

[code]
insert into TestTwo
SELECT t.*
FROM TestOne t
INNER JOIN (SELECT CID,MAX(Pvalue) AS MaxPValue
FROM TestOne
GROUP BY CID
)t1
ON t1.CID = t.CID
WHERE t1.MaxPValue <= 4
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-18 : 15:36:14

Hello, visakh16:

Thank you very much for the quick response. I may not state it clearly.

If Pvalue <= 4, all CID rows record associated with it will not be insert into table: TestTwo. For exmaple, CID 23231 has Pvalue > 4, so all CID 23231 rows will not be inserted into table: TestTwo. Only CID 12121 rows will be inserted into table: TestTwo.

The query you gave will insert CID: 23231 rows except row have Pvalue > 4, which is not what I mean.

Do you know how to accomplish it?

As always, your help is highly appreciated.

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-21 : 11:05:24

Sorry to both again. I have an urgent help for this. Is anyone who could help me out for this issue.

If Max( Pvalue) > 4, all CID rows record associated with it will not be insert into table: TestTwo. For example, CID 23231 has Max(Pvalue) > 4, so all CID 23231 rows will not be inserted into table: TestTwo. Only CID 12121 rows will be inserted into table: TestTwo.

Thank you very much.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-21 : 12:03:34

I'm not 100% I'm following the logic. If this query doesn't give you the results you want, please post which rows you expect to be inserted given your sample data.

insert into TestTwo
SELECT t.*
FROM TestOne t
INNER JOIN (SELECT CID,MAX(Pvalue) AS MaxPValue
            FROM TestOne
            GROUP BY CID
			HAVING MAX(Pvalue) <= 4
            )t1
ON t1.CID = t.CID

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-21 : 16:33:05

Thank you very much for all. I have figured it out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 19:02:37

quote:
Originally posted by snow12

Thank you very much for all. I have figured it out.

i'm sure you were not applying the correct group which caused it to return unwanted rows too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources