SELECT all negative values, that have a positive value

Question

SELECT all negative values, that have a positive value

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Thom A - Wednesday, January 10, 2018 2:04 AM
Thom A - Tuesday, January 9, 2018 2:54 PM
J Livingston SQL - Tuesday, January 9, 2018 10:39 AM
drew.allen - Tuesday, January 9, 2018 10:36 AM
J Livingston SQL - Tuesday, January 9, 2018 10:31 AM
wild guess.....
SELECT ABS(id) FROM yourtable GROUP BY ABS(id) HAVING(COUNT(DISTINCT grp) > 1);
I'm still thinking about this, but this query won't work, because it will also give you values where both groups contain the same value (either both positive or both negative).
Drew
agreed Drew......tis not correct
Could you not also add AND COUNT(DISTINCT id) > 1 to the HAVING clause? That would mean that if 104 had grp values of 1 and 2 it would be excluded, but 105/-105 with grp values of 5 and 6 would not.
At a PC now.
Using John's answer:
SELECT ABS(id) AS AbsId FROM @data GROUP BY ABS(id) HAVING COUNT(DISTINCT grp) > 1 AND COUNT(DISTINCT Id) > 1;

Yeah, that should work if all that matters is getting any negative id that has an occurrence in one group, and an occurrence of its positive counterpart in another group, regardless of which group that is in.

If you also want to know in which group there was a negative id that had a positive counterpart in another group, then something like my or Luis' query is necessary.

Concretely, for a set like this { (-103,1),(103,1),(-103,2) } are we fine just returning -103, or do we want to know that it's -103 in group 2 that has a positive counterpart in another group (the -103 in group 1 does not have its positive counterpart appear in another group).

From the description of the problem it sounded like the extra detail about groups might be needed, so I wrote my query for that. If that extra detail is not needed, then this query from Thom is almost certainly the best option 🙂

adiedler Old Hand Points: 391 More actions · Answer 2

Jacob Wilkins - Wednesday, January 10, 2018 8:26 AM
Thom A - Wednesday, January 10, 2018 2:04 AM
Thom A - Tuesday, January 9, 2018 2:54 PM
J Livingston SQL - Tuesday, January 9, 2018 10:39 AM
drew.allen - Tuesday, January 9, 2018 10:36 AM
J Livingston SQL - Tuesday, January 9, 2018 10:31 AM
wild guess.....
SELECT ABS(id) FROM yourtable GROUP BY ABS(id) HAVING(COUNT(DISTINCT grp) > 1);
I'm still thinking about this, but this query won't work, because it will also give you values where both groups contain the same value (either both positive or both negative).
Drew
agreed Drew......tis not correct
Could you not also add AND COUNT(DISTINCT id) > 1 to the HAVING clause? That would mean that if 104 had grp values of 1 and 2 it would be excluded, but 105/-105 with grp values of 5 and 6 would not.
At a PC now.
Using John's answer:
SELECT ABS(id) AS AbsId FROM @data GROUP BY ABS(id) HAVING COUNT(DISTINCT grp) > 1 AND COUNT(DISTINCT Id) > 1;
Yeah, that should work if all that matters is getting any negative id that has an occurrence in one group, and an occurrence of its positive counterpart in another group, regardless of which group that is in.
If you also want to know in which group there was a negative id that had a positive counterpart in another group, then something like my or Luis' query is necessary.
Concretely, for a set like this { (-103,1),(103,1),(-103,2) } are we fine just returning -103, or do we want to know that it's -103 in group 2 that has a positive counterpart in another group (the -103 in group 1 does not have its positive counterpart appear in another group).
From the description of the problem it sounded like the extra detail about groups might be needed, so I wrote my query for that. If that extra detail is not needed, then this query from Thom is almost certainly the best option 🙂

Thank you all for your helpful comments and good examples. I took it to my code and it works perfectly. My task was solved now.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

adiedler - Tuesday, January 9, 2018 10:05 AM

>> Ok, it is more or less a general question. <<

We still need the DDL, valid data element names. Here is my free labor and some questions.

CREATE TABLE Foobar
(foo_id INTEGER NOT NULL,
foo_grp INTEGER NOT NULl,
PRIMARY KEY (foo_id, foo_grp));

INSERT INTO Foobar
VALUES
(100,1), (101,1), (102,1), (103,3), (-103,3), (-102,2);

>> I want to have a select, that returns me all obj_ids, that have a negative and positive value NOT in the same group. <<

I had to assume a key, since you did not give us any help. Are these al intgers (floating point equality is different). What if a foo_id is in multiple groups? What about zero?

SELECT MIN(foo_id)
FROM Foobar
GROUP BY ABS(foo_id)
HAVING MIN(grp_id) < MAX(grp_id);

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101078 More actions · Answer 4

Another alternative; you could list up to 3 distinct grp values this way -- beyond 3 you would need to use a different method.

SELECT ABS(id) * -1 AS Id, MIN(grp) AS grp_min, MAX(grp) AS grp_max FROM @data GROUP BY ABS(id) HAVING MIN(SIGN(id)) = -1 AND MAX(SIGN(id)) = +1 AND MIN(grp) <> MAX(grp)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".