Query - 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 2008 Forums

Transact-SQL (2008)

Query

Author

Topic

Loneliness
Starting Member

10 Posts

Posted - 2014-02-12 : 10:14:31

Hi everybody, i have a table with this structure:

MyTable (Year, Name, Sex)

I need a query that for a certain year, if in that year in the sex field i have both values 'M' and 'F', shows only the rows when Sex='F'. But if i have only 'M' for that year i want it to show all the rows..
Example

Year, Name, Sex
2010 james M
2010 Rita F
2010 Peter M
2010 Stefany F
2011 Ivan M
2011 Mark M
2012 Sofia F
2012 Frank M
2013 Samir M

the query should return the following rows:

Year, Name, Sex
2010 Rita F -- because for 2010 i have both sex values
2010 Stefany F
2011 Ivan M -- because for 2011 i have only M as sex value
2011 Mark M
2012 Sofia F -- like 2010
2013 Samir M -- i have only M values

Thanks in advance

Loneliness

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-02-12 : 12:03:34

It is best to use ISO standards:

http://en.wikipedia.org/wiki/ISO_5218


-- *** Test Data
CREATE TABLE #MyTable
(
	[Year] smallint NOT NULL
	,Name varchar(20) NOT NULL
	,Sex tinyint NOT NULL
);
INSERT INTO #MyTable
VALUES (2010, 'james', 1)
	,(2010, 'Rita', 2)
	,(2010, 'Peter', 1)
	,(2010, 'Stefany', 2)
	,(2011, 'Ivan', 1)
	,(2011, 'Mark', 1)
	,(2012, 'Sofia', 2)
	,(2012, 'Frank', 1)
	,(2013, 'Samir', 1);
-- *** End Test Data
WITH MYears
AS
(
	SELECT [Year]
	FROM #MyTable
	GROUP BY [Year]
	HAVING COUNT(1) = SUM(CASE WHEN Sex = 1 THEN 1 ELSE 0 END)
)
SELECT T.[Year], T.Name, T.Sex
FROM #MyTable T
	LEFT JOIN MYears M
		ON T.[Year] = M.[Year]
WHERE Sex = 2 OR M.[Year] IS NOT NULL;

Loneliness
Starting Member

10 Posts

Posted - 2014-02-13 : 05:10:46

Thank you very much Ifor...this works perfectly!

quote:

Originally posted by Ifor

It is best to use ISO standards:

http://en.wikipedia.org/wiki/ISO_5218


-- *** Test Data
CREATE TABLE #MyTable
(
	[Year] smallint NOT NULL
	,Name varchar(20) NOT NULL
	,Sex tinyint NOT NULL
);
INSERT INTO #MyTable
VALUES (2010, 'james', 1)
	,(2010, 'Rita', 2)
	,(2010, 'Peter', 1)
	,(2010, 'Stefany', 2)
	,(2011, 'Ivan', 1)
	,(2011, 'Mark', 1)
	,(2012, 'Sofia', 2)
	,(2012, 'Frank', 1)
	,(2013, 'Samir', 1);
-- *** End Test Data
WITH MYears
AS
(
	SELECT [Year]
	FROM #MyTable
	GROUP BY [Year]
	HAVING COUNT(1) = SUM(CASE WHEN Sex = 1 THEN 1 ELSE 0 END)
)
SELECT T.[Year], T.Name, T.Sex
FROM #MyTable T
	LEFT JOIN MYears M
		ON T.[Year] = M.[Year]
WHERE Sex = 2 OR M.[Year] IS NOT NULL;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 08:12:42

No need of join. simply this would do


SELECT [Year], Name, Sex
FROM
(
SELECT SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END) OVER (PARTITION BY [Year]) AS CntF,*
FROM Table
)t
WHERE (CntF =0
OR Sex = 'F')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Loneliness
Starting Member

10 Posts

Posted - 2014-02-13 : 11:34:39

Mr Visakh i can only repeat what i thought when i saw your solution...WOW!

Thanks a lot.

*****************

quote:
Originally posted by visakh16

No need of join. simply this would do

SELECT [Year], Name, Sex
FROM
(
SELECT SUM(CASE WHEN Sex = 'F' THEN 1 ELSE 0 END) OVER (PARTITION BY [Year]) AS CntF,*
FROM Table
)t
WHERE (CntF =0
OR Sex = 'F')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 12:11:45

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources