Select Statement to get desired result.

SQL Query

Select Col_A, "Max_Of(Col_B) only if COL_C = Z", Col_C, Col_D

Sample Data:
COL_A    COL_B     COL_C   COL_D
143362   123001    A       Approved
143362   123002    P       Pending
143362   123003    E       Expired
143362   123004    C       Cancelled
143362   123005    Z       Unknown

143363   123001    A       Approved
143363   123002    P       Pending

143364   123001    A       Approved
143364   123002    P       Pending

143365   123001    A       Approved
143365   123002    z       Unknown

143366   123001    A       Approved
143366   123002    P       Pending
143366   123003    Z       Unknown
143366   123004    P       Pending

143367   123001    A       Approved
143367   123002    z       Unknown
143367   123003    A       Approved

143368   123001    Z       Unknown
143368   123002    A       Approved

143369   123001    A       Approved
143369   123002    C       Cancelled
143369   123003    z       Unknown
--##################################

The out put should be:
COL_A    COL_B     COL_C   COL_D
143362   123005    Z       Unknown
143365   123002    z       Unknown
143369   123003    z       Unknown
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
I have tested this snippet!
DECLARE @table TABLE (
	Col_A INT,
	Col_B INT,
	col_C	VARCHAR(10),
	Col_D	VARCHAR(20))


INSERT INTO @table ( Col_A, Col_B, col_C, Col_D )

VALUES 
  (143362, 123001, 'A', 'Approved ')
, (143362, 123002, 'P', 'Pending	 ')
, (143362, 123003, 'E', 'Expired	 ')
, (143362, 123004, 'C', 'Cancelled')
, (143362, 123005, 'Z', 'Unknown	 ')
, (143363, 123001, 'A', 'Approved ')
, (143363, 123002, 'P', 'Pending	 ')
, (143364, 123001, 'A', 'Approved ')
, (143364, 123002, 'P', 'Pending	 ')
, (143365, 123001, 'A', 'Approved ')
, (143365, 123002, 'z', 'Unknown	 ')
, (143366, 123001, 'A', 'Approved ')
, (143366, 123002, 'P', 'Pending	 ')
, (143366, 123003, 'Z', 'Unknown	 ')
, (143366, 123004, 'P', 'Pending	 ')
, (143367, 123001, 'A', 'Approved ')
, (143367, 123002, 'z', 'Unknown	 ')
, (143367, 123003, 'A', 'Approved ')
, (143368, 123001, 'Z', 'Unknown	 ')
, (143368, 123002, 'A', 'Approved ')
, (143369, 123001, 'A', 'Approved ')
, (143369, 123002, 'C', 'Cancelled')
, (143369, 123003, 'z', 'UNKNOWN	 ')


SELECT A.Col_A, A.Col_B, A.col_C, A.Col_D 
FROM (
	SELECT * , ROW_NUMBER() OVER(PARTITION BY Col_A ORDER BY Col_B DESC ) AS RowNumber
	FROM @table
) AS A
WHERE col_C = 'Z' 
AND A.RowNumber = 1

Open in new window

1
_agx_Commented:
Not tested, but ...If you mean when the maximum COL_B number has a COL_C value of "Z"

SELECT a.COL_A
              , a.COL_B
              , a.COL_C
              , a.COL_D
FROM YourTable a INNER JOIN
            (
                  SELECT COL_A, MAX(COL_B) AS COL_B
                  FROM   YourTable
                  GROUP BY COL_A
            ) z ON z.COL_A = a.COL_A AND z.COL_B = a.COL_B AND a.COL_C = 'Z'
0
_agx_Commented:
Just confirmed my query works (with Éric's data), but ... I'd go with Éric's. Cleaner syntax IMO. Also handles dupes, mine doesn't.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Kamal AgnihotriAuthor Commented:
Hi Experts,

Thanks for the efforts.

How would Eric's Query be different if it was to run against MS. Access Database?

I guess you all tried Oracle as DBMS.
0
Éric MoreauSenior .Net ConsultantCommented:
that would have been nice to know earlier! I have used Microsoft SQL Server to create the snippet.
0
Kamal AgnihotriAuthor Commented:
Sorry about that. I am working in an MS. Access Database. Once again, I am so sorry.
0
_agx_Commented:
I don't think Access supports ROW_NUMBER(). Can there be  duplicate rows with the same COL_A + COL_B + COL_C (where value == "Z") combination? If not, mine should work. Otherwise, is there a date or a unique ID to use to break the tie?

>> Sorry about that. I am working in an MS. Access Database.
Edit: S'Okay, but do always include your DBMS in the tags (or question). Many things are vendor specific, so letting us know which DBMS you're using will help get you the correct answer sooner.
0
Kamal AgnihotriAuthor Commented:
regarding solution by LVL53 (_agx_), When running against  Ms. Access database, I am getting error: Join expression not supported.

The Select statement is:

Select W.ID, W.Application_ID, W.New_Status_Code, W.New_status
From Status W
Inner Join (Select ID, Max(Application _ID) As Application_ID
                  From Status
                  Group By ID) K
On K.ID = W.ID
AND K. Application_ID =  W. Application_ID
AND W.New_Status_Code = 'A';

I think we are very close. The next correction from you should fix it. Thanks a lot.
0
_agx_Commented:
Update: Fix SQL Syntax

I don't use Access much anymore, but am pretty sure it does supports that type of JOIN. Though I seem to remember it requiring extra parenthesis all over the place or it choked. Try wrapping it like this:

SELECT T.ID, T.Application_ID, T.New_Status_Code, T.New_status
FROM    (Status W INNER JOIN
                  ( SELECT ID, Max(Application _ID) As Application_ID
                    FROM     Status
                    GROUP BY ID
                  ) K ON K.ID = W.ID
                       AND K. Application_ID =  W.Application_ID
                      AND W.New_Status_Code = 'A'
           ) T
0
_agx_Commented:
Finally found a machine with Access.  I forgot how picky it is about some things. This syntax worked with Access 2013

SELECT a.COL_A
              , a.COL_B
              , a.COL_C
              , a.COL_D
FROM YourTable a INNER JOIN
            (
                  SELECT COL_A, MAX(COL_B) AS MAX_COL_B
                  FROM   YourTable
                  GROUP BY COL_A
            ) AS z
             ON z.COL_A = a.COL_A AND z.MAX_COL_B = a.COL_B
WHERE   a.COL_C = 'Z'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kamal AgnihotriAuthor Commented:
Issue resolved. Question closed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.