help with the SELECT statement syntax.

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  January 26, 2018 at 10:18 am

  #1977557

  mw112009 - Wednesday, January 24, 2018 3:33 PM

  if object_id('tempdb..#rx') IS NOT NULL DROP TABLE #rx
  GO

  create table #rx( MemN VARCHAR(15), ClaimNumber VARCHAR(20), RX_Number VARCHAR(20), Claim_Type CHAR(1) ) ;
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462241','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462241','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795911','000000000018','P');

  /*
  Question: I want a Select statement that will only give the last row as a hit.
  Why ? The first line is an Adjustmet "A" that was made against the second row which as a "P"
  Both rows have same RX and ClaimNumber ( So they both have to be eliminated ) 
  The third row is a new entry made ( i only need the third row ) 
  */

  /*
  Question: I want a Select statement that will only give the last row as a hit.
  Why ? The first line is an Adjustmet "A" that was made against the second row which as a "P"
  Both rows have same RX and ClaimNumber ( So they both have to be eliminated )
  The third row is a new entry made ( i only need the third row )
  */

  By definition, a table must have a key, but what you posted is basically a deck of punch cards or notes on the pad. Since everything could benull, there was no way for it to ever have a key. here’s my guess at what you might have meant

  CREATETABLE Prescriptions

  (memn VARCHAR(15) NOT NULL,

  claim_nbr VARCHAR(20) NOT NULL,

  rx_nbr VARCHAR(20) NOT NULL,

  claim_typ CHAR(1) NOT NULL

  CHECK(claim_type IN (‘A’, ‘P’))

  PRIMARY KEY (memn, claim_nbr, rx_nbr));

  italso makes no sense to talk about the third row, last row, or thefirst line in a table. Rows of a table have no ordering bydefinition. If you’d bothered to read any book on RDBMS, you do runinto a thing called Codd’s Information Principle. It states thatall relationships are shown by scalar values in the columns of the rows of a table. That means we need to have some kind of ordering puton this thing. Because I am guessing that it’s prescriptions fromthe RX, I would guess that you need a timestamp that you don’thave.

  INSERT INTO Prescriptions(memn, claim_nbr, rx_nbr, claim_type)

  VALUES
('00045199701','126278462241','000000000018','A'),

  ('00045199701','126278462241','000000000018','P'),

  ('00045199701','126576795911','000000000018','P');

  /*Question: I want a Select statement that will only give the last[sic] row as a hit.

  Why?The first [sic] line is an Adjustment "A" that was madeagainst the second [sic] row which as a "P"

  Bothrows have same rx_nbr and claim_nbr (So they both have to beeliminated)

  Thethird [sic] row is a new entry made (I only need the third [sic] row)*/

  I’mmaking a guess that you want to use a claim type of ‘A’ tocounsel out one of type ‘P’, without any regard to any ordering.here’s one possible way, using set level operations.

  SELECTmemn, claim_nbr, rx_nbr

  FROMPrescriptions

  EXCEPT

  SELECTmemn, claim_nbr, rx_nbr

  FROMPrescriptions

  GROUPBY memn, claim_nbr, rx_nbr

  HAVINGMIN(claim_type) = ‘A’

    ANDMAX(claim_type) = ‘P’

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

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 26, 2018 at 11:42 am

  #1977568

  jcelko212 32090 - Friday, January 26, 2018 10:18 AM

  mw112009 - Wednesday, January 24, 2018 3:33 PM

  if object_id('tempdb..#rx') IS NOT NULL DROP TABLE #rx
  GO

  create table #rx( MemN VARCHAR(15), ClaimNumber VARCHAR(20), RX_Number VARCHAR(20), Claim_Type CHAR(1) ) ;
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462241','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462241','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795911','000000000018','P');

  /*
  Question: I want a Select statement that will only give the last row as a hit.
  Why ? The first line is an Adjustmet "A" that was made against the second row which as a "P"
  Both rows have same RX and ClaimNumber ( So they both have to be eliminated ) 
  The third row is a new entry made ( i only need the third row ) 
  */

  /*
  Question: I want a Select statement that will only give the last row as a hit.
  Why ? The first line is an Adjustmet "A" that was made against the second row which as a "P"
  Both rows have same RX and ClaimNumber ( So they both have to be eliminated )
  The third row is a new entry made ( i only need the third row )
  */

  By definition, a table must have a key, but what you posted is basically a deck of punch cards or notes on the pad. Since everything could benull, there was no way for it to ever have a key. here’s my guess at what you might have meant

  CREATETABLE Prescriptions

  (memn VARCHAR(15) NOT NULL,

  claim_nbr VARCHAR(20) NOT NULL,

  rx_nbr VARCHAR(20) NOT NULL,

  claim_typ CHAR(1) NOT NULL

  CHECK(claim_type IN (‘A’, ‘P’))

  PRIMARY KEY (memn, claim_nbr, rx_nbr));

  italso makes no sense to talk about the third row, last row, or thefirst line in a table. Rows of a table have no ordering bydefinition. If you’d bothered to read any book on RDBMS, you do runinto a thing called Codd’s Information Principle. It states thatall relationships are shown by scalar values in the columns of the rows of a table. That means we need to have some kind of ordering puton this thing. Because I am guessing that it’s prescriptions fromthe RX, I would guess that you need a timestamp that you don’thave.

  INSERT INTO Prescriptions(memn, claim_nbr, rx_nbr, claim_type)

  VALUES
('00045199701','126278462241','000000000018','A'),

  ('00045199701','126278462241','000000000018','P'),

  ('00045199701','126576795911','000000000018','P');

  /*Question: I want a Select statement that will only give the last[sic] row as a hit.

  Why?The first [sic] line is an Adjustment "A" that was madeagainst the second [sic] row which as a "P"

  Bothrows have same rx_nbr and claim_nbr (So they both have to beeliminated)

  Thethird [sic] row is a new entry made (I only need the third [sic] row)*/

  I’mmaking a guess that you want to use a claim type of ‘A’ tocounsel out one of type ‘P’, without any regard to any ordering.here’s one possible way, using set level operations.

  SELECTmemn, claim_nbr, rx_nbr

  FROMPrescriptions

  EXCEPT

  SELECTmemn, claim_nbr, rx_nbr

  FROMPrescriptions

  GROUPBY memn, claim_nbr, rx_nbr

  HAVINGMIN(claim_type) = ‘A’

    ANDMAX(claim_type) = ‘P’

  Your solution worked fine. I am pleased with Solution #1