SELECT all negative values, that have a positive value

• adiedler

  Old Hand

  Points: 391

  More actions

  January 9, 2018 at 9:55 am

  #402817

  Hello,
  I need to identify in SQL server table all entries with a negative value, that have a positive value.
  How I can select this?

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 9, 2018 at 10:01 am

  #1975116

  some sample data would help please.....your question doesnt really make any sense without data that shows your problem

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• adiedler

  Old Hand

  Points: 391

  More actions

  January 9, 2018 at 10:05 am

  #1975122

  Ok, it is more or less a general question.
  Table object with id and group as columns
  Data: 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.
  Return should be -102  beause 102 have positive and negative value and in differnt groups (1 and 2)

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 9, 2018 at 10:19 am

  #1975126

  first off...please read this link https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/    You will be surprised how you get faster and better answers by following the advice.
  secondly why return -102 and not 102?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 9, 2018 at 10:31 am

  #1975127

  wild guess.....

  SELECT ABS(id)
FROM yourtable
GROUP BY ABS(id)
HAVING(COUNT(DISTINCT grp) > 1);

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• drew.allen

  SSC Guru

  Points: 76984

  More actions

  January 9, 2018 at 10:36 am

  #1975130

  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

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• adiedler

  Old Hand

  Points: 391

  More actions

  January 9, 2018 at 10:38 am

  #1975131

  J Livingston SQL - Tuesday, January 9, 2018 10:19 AM

  first off...please read this link https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/    You will be surprised how you get faster and better answers by following the advice.
  secondly why return -102 and not 102?

  Thank you, this are good tipps to improve questions to ask here, next time. more or less, to ask a question with very simple table structure, only two columns It takes more time to generate all the example code staff etc instead of "try my luck here" to find a people to understand. 
   Need only the negative value because I have only to change these values with update script.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  January 9, 2018 at 10:39 am

  #1975132

  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

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 9, 2018 at 11:14 am

  #1975139

  Unless anyone has a better idea.
  
DECLARE @data TABLE(
  id int,
  grp int
);
INSERT INTO @data
VALUES ( 100,1), ( 101,1), ( 102,1), ( 103,3), ( -103,3), ( -102,2);

  SELECT *
FROM @data pos
JOIN @data neg ON pos.id = -neg.id
     AND pos.grp <> neg.grp
WHERE neg.id < 0


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 9, 2018 at 11:36 am

  #1975141

  Here's another way:

  WITH CTE AS
(
SELECT
id
,grp
,rnk  =RANK() OVER (PARTITION BY ABS(id) ORDER BY ID DESC)
,grp_rnk =RANK() OVER (PARTITION BY ABS(id), grp ORDER BY ID DESC)
FROM @data
)

  SELECT id, grp
FROM CTE
WHERE rnk>grp_rnk;

  Cheers!

• Thom A

  SSC Guru

  Points: 99127

  More actions

  January 9, 2018 at 2:54 pm

  #1975182

  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.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  January 9, 2018 at 5:17 pm

  #1975199

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

  J Livingston SQL - Tuesday, January 9, 2018 10:19 AM

  first off...please read this link https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/    You will be surprised how you get faster and better answers by following the advice.
  secondly why return -102 and not 102?

  Thank you, this are good tipps to improve questions to ask here, next time. more or less, to ask a question with very simple table structure, only two columns It takes more time to generate all the example code staff etc instead of "try my luck here" to find a people to understand. 
   Need only the negative value because I have only to change these values with update script.

  It likely took you as long to post the non-consumable data as it would have to post the consumable data.  The difference between this set of forums and most other is that people like to make sure they've tested their code first so they don't have to admit posting a possible swag or other unconfirmed bit of code... and it's seriously to your benefit.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jonathan.crawford

  SSCertifiable

  Points: 6612

  More actions

  January 9, 2018 at 7:39 pm

  #1975209

  This is why I hate quantum computing

  -------------------------------------------------------------------------------------------------------------------------------------
  Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  January 9, 2018 at 9:44 pm

  #1975215

  jonathan.crawford - Tuesday, January 9, 2018 7:39 PM

  This is why I hate quantum computing

  But it's essential to the proper operation of DBCC TIMEWARP. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Thom A

  SSC Guru

  Points: 99127

  More actions

  January 10, 2018 at 2:04 am

  #1975229

  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;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk