if c1=<5 once then don't get rows with c2=17. How? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

if c1=<5 once then don't get rows with c2=17. How?

Author

Topic

JoeMa
Starting Member

5 Posts

Posted - 2009-01-21 : 15:48:26

Hi,

I've got the following riddle and I don't get it

:

Table:

The wanted result:
Every c1 which has only c2's > 5

The example above should therefor provide the result:
c1
====
3
5

Is it possible with SQL?

Thx in advance

bye,
JoeMa

edit: typos

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 15:50:46

This must be a homework question, but I'll bite anyway.

SELECT c1
FROM YourTable
WHERE c2 > 5

Add DISTINCT if needed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JoeMa
Starting Member

5 Posts

Posted - 2009-01-21 : 16:00:18

Thx for your answer.

Maybe I was unclear.
I'll try to describe what I need:

If a row has c2 <= 5 at least one time then don't put any rows to result with the corresponding c1 of that row.

Hope it's more uncerstandable?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-21 : 16:13:31

I think I understand it now. Try this:


SELECT c1
FROM
(
	SELECT c1, MIN(c2) AS c2
	FROM YourTable
	GROUP BY c1
) t
WHERE c2 > 5

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-21 : 16:38:51

Since only c1 is used in the output, you can use the simpler

SELECT c1 FROM YourTable GROUP BY c1 HAVING MIN(c2) > 5

E 12°55'05.63"
N 56°04'39.26"

JoeMa
Starting Member

5 Posts

Posted - 2009-01-21 : 16:53:18

Both solutions work.

Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources