help with the SELECT statement syntax.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 24, 2018 at 3:33 pm

  #343424

  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 )
  */

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  January 24, 2018 at 4:02 pm

  #1977231

  Here are 2 simplistic ways to return the 3rd (last) row. I'm betting there is something more complicated where this would be used, but this is a starting point!

  select MemN, ClaimNumber
  from #rx
  group by MemN, ClaimNumber
  Having count(*) = 1;
  
  select MemN, ClaimNumber, RX_Number
  from (
   Select MemN, ClaimNumber, RX_Number,
      Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
    from #Rx
  ) r
  where r.RowNum = 1;
  

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 11:59 am

  #1977412

  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');
INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','A');
INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','P');
INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795912','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 )
*/

  select MemN, ClaimNumber, COUNT(*)
from #rx
group by MemN, ClaimNumber
Having count(*) = 1;

  select MemN, ClaimNumber, RX_Number
from (
Select MemN, ClaimNumber, RX_Number,
  Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
from #Rx
) r
where r.RowNum = 1;

  Solution #2 Does not work with a larger dataset. Still returns one row.
  

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  January 25, 2018 at 12:53 pm

  #1977419

  mw112009 - Thursday, January 25, 2018 11:59 AM

  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');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795912','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 )
  */

  select MemN, ClaimNumber, COUNT(*)
  from #rx
  group by MemN, ClaimNumber
  Having count(*) = 1;

  select MemN, ClaimNumber, RX_Number
  from (
  Select MemN, ClaimNumber, RX_Number,
    Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
  from #Rx
  ) r
  where r.RowNum = 1;

  Solution #2 Does not work with a larger dataset. Still returns one row.
  

  You've been around long enough to know that we always ask for expected results.  You failed to provide us with expected results and then complain when the results don't match your expectations.  If you can't put in enough effort to fulfill your end of the bargain, I see no reason to put in the effort to figure out what you really want.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  January 25, 2018 at 1:03 pm

  #1977420

  mw112009 - Thursday, January 25, 2018 11:59 AM

  
Solution #2 Does not work with a larger dataset. Still returns one row.


  What's wrong with solution 1?

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 1:06 pm

  #1977421

  drew.allen - Thursday, January 25, 2018 12:53 PM

  mw112009 - Thursday, January 25, 2018 11:59 AM

  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');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795912','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 )
  */

  select MemN, ClaimNumber, COUNT(*)
  from #rx
  group by MemN, ClaimNumber
  Having count(*) = 1;

  select MemN, ClaimNumber, RX_Number
  from (
  Select MemN, ClaimNumber, RX_Number,
    Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
  from #Rx
  ) r
  where r.RowNum = 1;

  Solution #2 Does not work with a larger dataset. Still returns one row.
  

  You've been around long enough to know that we always ask for expected results.  You failed to provide us with expected results and then complain when the results don't match your expectations.  If you can't put in enough effort to fulfill your end of the bargain, I see no reason to put in the effort to figure out what you really want.

  Drew

  Mr Drew: Solution #1 works for me.  The only reason why I mentioned Solution #2 does not work is for information purposes ( Just to convey that it would not work with a large data set ). No hard feelings and Thank you for every effort.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 1:10 pm

  #1977422

  LinksUp - Thursday, January 25, 2018 1:03 PM

  mw112009 - Thursday, January 25, 2018 11:59 AM

  
Solution #2 Does not work with a larger dataset. Still returns one row.


  What's wrong with solution 1?

  "Hall of Fame" : The purpose here is to get a SELECT statement that will eliminate the pairs and only return the rows that don't have  a partner. So if you run Solution #2 ( Use the table which has 6 rows ) it returns only one row. it should return 2 rows ( because there are 2 rows that dont have a matching partner ). I'd say run both SQl statements and you will see that #1 returns 2 rows and #2 only returns one row.

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 1:30 pm

  #1977424

  LinksUp - Thursday, January 25, 2018 1:03 PM

  mw112009 - Thursday, January 25, 2018 11:59 AM

  
Solution #2 Does not work with a larger dataset. Still returns one row.


  What's wrong with solution 1?

  Now it works with the following

  
Select * FROM
#rx A
LEFT JOIN
(
select MemN, ClaimNumber, RX_Number
from (
Select MemN, ClaimNumber, RX_Number,
  ROW_NUMBER() over(Partition by MemN, ClaimNumber Order by ClaimNumber desc) RowNum
from #Rx
) r
where r.RowNum > 1
)B
ON (A.ClaimNumber = B.ClaimNumber) AND (A.MemN = B.MemN)
WHERE
B.MemN IS NULL


• drew.allen

  SSC Guru

  Points: 76988

  More actions

  January 25, 2018 at 2:29 pm

  #1977438

  mw112009 - Thursday, January 25, 2018 1:06 PM

  drew.allen - Thursday, January 25, 2018 12:53 PM

  mw112009 - Thursday, January 25, 2018 11:59 AM

  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');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795912','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 )
  */

  select MemN, ClaimNumber, COUNT(*)
  from #rx
  group by MemN, ClaimNumber
  Having count(*) = 1;

  select MemN, ClaimNumber, RX_Number
  from (
  Select MemN, ClaimNumber, RX_Number,
    Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
  from #Rx
  ) r
  where r.RowNum = 1;

  Solution #2 Does not work with a larger dataset. Still returns one row.
  

  You've been around long enough to know that we always ask for expected results.  You failed to provide us with expected results and then complain when the results don't match your expectations.  If you can't put in enough effort to fulfill your end of the bargain, I see no reason to put in the effort to figure out what you really want.

  Drew

  Mr Drew: Solution #1 works for me.  The only reason why I mentioned Solution #2 does not work is for information purposes ( Just to convey that it would not work with a large data set ). No hard feelings and Thank you for every effort.

  Again, this comes down to communication.  You only mentioned Solution #2, so the implication is that you have decided on that approach and have either not considered Solution #1 or you have already ruled it out.

  Also, Solution #2 doesn't work, because you, again, failed to communicate properly.  You said that you wanted the last one, so that is what you got.  Now you are saying that you want singletons, which is a completely different problem.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  January 25, 2018 at 2:35 pm

  #1977439

  mw112009 - Thursday, January 25, 2018 1:10 PM

  "Hall of Fame" : The purpose here is to get a SELECT statement that will eliminate the pairs and only return the rows that don't have  a partner. So if you run Solution #2 ( Use the table which has 6 rows ) it returns only one row. it should return 2 rows ( because there are 2 rows that dont have a matching partner ). I'd say run both SQl statements and you will see that #1 returns 2 rows and #2 only returns one row.

  Given this NEW description of the problem.  Here is a much better approach.

  SELECT MemN, ClaimNumber, RX_Number, MAX(Claim_Type)
FROM #rx
GROUP BY MemN, ClaimNumber, RX_Number
HAVING COUNT(*) = 1

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 3:01 pm

  #1977442

  drew.allen - Thursday, January 25, 2018 2:29 PM

  mw112009 - Thursday, January 25, 2018 1:06 PM

  drew.allen - Thursday, January 25, 2018 12:53 PM

  mw112009 - Thursday, January 25, 2018 11:59 AM

  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');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','A');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126278462242','000000000018','P');
  INSERT INTO #rx(MemN,ClaimNumber,RX_Number,Claim_Type) VALUES ('00045199701','126576795912','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 )
  */

  select MemN, ClaimNumber, COUNT(*)
  from #rx
  group by MemN, ClaimNumber
  Having count(*) = 1;

  select MemN, ClaimNumber, RX_Number
  from (
  Select MemN, ClaimNumber, RX_Number,
    Row_Number() over(Partition by MemN Order by ClaimNumber desc) RowNum
  from #Rx
  ) r
  where r.RowNum = 1;

  Solution #2 Does not work with a larger dataset. Still returns one row.
  

  You've been around long enough to know that we always ask for expected results.  You failed to provide us with expected results and then complain when the results don't match your expectations.  If you can't put in enough effort to fulfill your end of the bargain, I see no reason to put in the effort to figure out what you really want.

  Drew

  Mr Drew: Solution #1 works for me.  The only reason why I mentioned Solution #2 does not work is for information purposes ( Just to convey that it would not work with a large data set ). No hard feelings and Thank you for every effort.

  Again, this comes down to communication.  You only mentioned Solution #2, so the implication is that you have decided on that approach and have either not considered Solution #1 or you have already ruled it out.

  Also, Solution #2 doesn't work, because you, again, failed to communicate properly.  You said that you wanted the last one, so that is what you got.  Now you are saying that you want singletons, which is a completely different problem.

  Drew

  Mr Drew: Agree, Keep in mind  there are people whose communication skills are not to the level you expect. Please be kind enough to understand the customer needs. Even if I made a mistake, so what ?. There is no rule that says you have to explain the issue perfectly.  Learn to appreciate the little good I did. At least I posted some sample code for you to run.  Be of value to the customer and don't learn to criticize.

• below86

  SSChampion

  Points: 11532

  More actions

  January 25, 2018 at 3:12 pm

  #1977445

  Just to see how bad I get pounded for this code. This will give you any 'A' claim types where you don't have a 'P' or all 'P' that don't have a matching 'A'.
  
(SELECT MemN, ClaimNumber, RX_Number
FROM #rx
WHERE Claim_Type = 'A'
EXCEPT
SELECT MemN, ClaimNumber, RX_Number
FROM #rx
WHERE Claim_Type = 'P')
UNION ALL
(SELECT MemN, ClaimNumber, RX_Number
FROM #rx
WHERE Claim_Type = 'P'
EXCEPT
SELECT MemN, ClaimNumber, RX_Number
FROM #rx
WHERE Claim_Type = 'A')
;
  Drew's is simpler.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 25, 2018 at 3:34 pm

  #1977448

  mw112009 - Thursday, January 25, 2018 3:01 PM

  Mr Drew: Agree, Keep in mind  there are people whose communication skills are not to the level you expect. Please be kind enough to understand the customer needs. Even if I made a mistake, so what ?. There is no rule that says you have to explain the issue perfectly.  Learn to appreciate the little good I did. At least I posted some sample code for you to run.  Be of value to the customer and don't learn to criticize.

  A customer is generally considered to be one that purchases goods or services - you aren't paying for anything 🙂
  Everyone up here answering questions is a volunteer and giving up their time to try to help people out. I think everyone would agree that not everyone has the same level of communication skills. But it actually is a big deal if someone spends a lot of time doing queries a few times over when things aren't communicated well. That time could be used to answer several other posters where the question posted was clear. It's important to learn to appreciate the free help and someone taking time away from family, sleep, relaxation time to help out.

  Sue

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  January 25, 2018 at 3:54 pm

  #1977450

  Sue_H - Thursday, January 25, 2018 3:34 PM

  mw112009 - Thursday, January 25, 2018 3:01 PM

  Mr Drew: Agree, Keep in mind  there are people whose communication skills are not to the level you expect. Please be kind enough to understand the customer needs. Even if I made a mistake, so what ?. There is no rule that says you have to explain the issue perfectly.  Learn to appreciate the little good I did. At least I posted some sample code for you to run.  Be of value to the customer and don't learn to criticize.

  A customer is generally considered to be one that purchases goods or services - you aren't paying for anything 🙂
  Everyone up here answering questions is a volunteer and giving up their time to try to help people out. I think everyone would agree that not everyone has the same level of communication skills. But it actually is a big deal if someone spends a lot of time doing queries a few times over when things aren't communicated well. That time could be used to answer several other posters where the question posted was clear. It's important to learn to appreciate the free help and someone taking time away from family, sleep, relaxation time to help out.

  Sue

  Sue: I appreciate all the help. Keep in mind you don't get perfect customers ( or users ( instead of customers ) ) in this world. Some of us had to learn English as a second language. So it is no surprise that my communication was not clear.  I appreciate your volunteer time. However, I (The user ) did not ask anyone to volunteer. Plus if you ( or anyone who is volunteering ) thinks the communication is not adequate, don't bother to answer ( As simple as that ) . Why get pissed off ( after all you mentioned you are not getting paid ) and volunteer to help ?  In this case Mr Drew did not post the answer. It was someone else.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  January 25, 2018 at 4:57 pm

  #1977455

  mw112009 - Thursday, January 25, 2018 3:54 PM

  Sue_H - Thursday, January 25, 2018 3:34 PM

  mw112009 - Thursday, January 25, 2018 3:01 PM

  Mr Drew: Agree, Keep in mind  there are people whose communication skills are not to the level you expect. Please be kind enough to understand the customer needs. Even if I made a mistake, so what ?. There is no rule that says you have to explain the issue perfectly.  Learn to appreciate the little good I did. At least I posted some sample code for you to run.  Be of value to the customer and don't learn to criticize.

  A customer is generally considered to be one that purchases goods or services - you aren't paying for anything 🙂
  Everyone up here answering questions is a volunteer and giving up their time to try to help people out. I think everyone would agree that not everyone has the same level of communication skills. But it actually is a big deal if someone spends a lot of time doing queries a few times over when things aren't communicated well. That time could be used to answer several other posters where the question posted was clear. It's important to learn to appreciate the free help and someone taking time away from family, sleep, relaxation time to help out.

  Sue

  Sue: I appreciate all the help. Keep in mind you don't get perfect customers ( or users ( instead of customers ) ) in this world. Some of us had to learn English as a second language. So it is no surprise that my communication was not clear.  I appreciate your volunteer time. However, I (The user ) did not ask anyone to volunteer. Plus if you ( or anyone who is volunteering ) thinks the communication is not adequate, don't bother to answer ( As simple as that ) . Why get pissed off ( after all you mentioned you are not getting paid ) and volunteer to help ?  In this case Mr Drew did not post the answer. It was someone else.

  I'm not pissed off - that's an odd thing to say.
  I do understand the second language issue - one of my parents and everyone on that side all have English as their second language. I grew up communicating with broken English. I understand how that position is used at times. There are plenty of folks with fine English who go around and around with what they are trying to accomplish so it's not just an English speaking issues. From what I've seen, its more than that. But thanks, I'll work on ignoring more posts.

  Sue