I need to create a select with both a group by max date and using a where clause.

Or you might like ...

with target as
     (select   case_gen_nbr, max (status_date) statdate
          from case_status_hists csh
         where SUBSTR (org_code_r, 1, 1) = 'R'
      group by case_gen_nbr)
select csh.case_gen_nbr, csh.status_date statdate, csh.org_code_r reg
  from case_status_hists csh join target t
       on (t.case_gen_nbr = csh.case_gen_nbr and t.statdate = csh.statdate)
       ;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

Another hack way to do this is this; if you don't mind statdate and org being concatenated into a single column. If you have a massive amount of data, this will probably run faster.

select distinct csh.case_gen_nbr,
                max (TO_CHAR (csh.status_date,'yyyymmdd') 
                      || ' - ' 
                      || csh.org_code_r) statdate_and_org_code
           from case_status_hists csh
          where SUBSTR (csh.org_code_r, 1, 1) = 'R'
       group by csh.case_gen_nbr;

                                              

1:
2:
3:
4:
5:
6:
7:

Can you try the below :

SELECT csh.case_gen_nbr, MAX (csh.status_date) statdate
   FROM case_status_hists csh
WHERE SUBSTR (csh.org_code_r, 1, 1) = 'R'
 GROUP BY csh.case_gen_nbr
order by 1;

I think this should get what you are looking for. Thanks,

mmoore:

You are really close with your 2nd post. I need the region (R#) in the final result set by itself. But, it does look like you retreiving the region with the latest date per each ID, that part is correct.

thank-you!

John

john@abdev.net

If I change it to have the region field (org_code_r) by itself it gives me more than one region in the result.

SELECT DISTINCT csh.case_gen_nbr,
MAX (csh.status_date),
csh.org_code_r
reg
FROM case_status_hists csh
WHERE SUBSTR (csh.org_code_r, 1, 1) = 'R'
GROUP BY csh.case_gen_nbr, csh.org_code_r

Which you can see in the code snippet below on the second to last record.

CASE_GEN_NBR	MAX(CSH.STATUS_DATE)	REG
 
11	2/7/2001	R01
29	12/31/1997	R01
36	10/2/2003	R01
66	3/25/1998	R03
69	6/17/2002	R01
70	11/10/1999	R01
83	2/6/1998	R01
92	8/28/1998	R02
113	1/29/1999	R01
139	7/1/1997	R01
162	6/30/1997	R02
166	9/29/1997	R02
167	9/29/1997	R02
174	9/1/1997	R02
178	5/21/1999	R02
191	4/9/1999	R03
191	6/30/2005	R14
194	6/30/1997	R02
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

That is why I posted the more complex solution ... If you group by region code, you get each region code. Point.

Qlemo:

The problem with yours is that it looks for the max region. I might be R01 or R14 that is the latest date. But I agree you have addressed the issue in your code. I also got invalid column name in Toad when I pasted all.

thank-you Qlemo,

John

It looks for the max region ID with that date, that much is true. You HAVE TO decide which region to choose if there are multiple records with same statdate. Max is only one decision, it could be count, min, avg. With your example data, there is no conflict, and the results will give you the correct region:
1, 09-21-2008, r01
2, 09-22-2008, r08
3, 11-13-2007, r03
As an alternative you could this, as it would give you all regions with the same newest statdate:

select csh.case_gen_nbr, csh.status_date statdate,
                         csh.org_code_r reg
FROM case_status_hists csh,
  (select case_gen_nbr, max(csh.status_date) statdate
   from case_status_hists
   WHERE SUBSTR (org_code_r, 1, 1) = 'R'
   GROUP BY case_gen_nbr) grp
where csh.status_date = grp.statdate
  and csh.case_gen_nbr = grp.case_gen_nbr;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

mmoore:

The latest one looks good so far, I am testing it out right now. I didn't understand your very first one. It had a "with target as" in front, I've never seen that. When I pasted it into Toad it gave me an error and I didn't know if that was part of the statement or not.

Anyway, I should know within an hour if this new code is working properly. There are 200K records in the table and I need to check it really good. This is for a state level audit.

I will reply by 7:30 EST.

I ended up being 232,000 in the end. And the data passed all of the tests I through at it. Thank-you mmoore, that was what I needed.

There was one typo (case_gen), but there were several in my comments, so it's all good. I figured it out quickly and knew what you meant. The answer you provided me was exactly what I needed.
 thank-you

Not sure why the "WITH" syntax did not work for your TOAD. Perhaps you have an older version of TOAD.

In  "WITH TARGET", TARGET is simply a temporary table name. I could have called it "COW" or "Azeroth".

"WITH" says ... build a temporary table that looks like this. Then after that, the SELECT part of the statement uses that temporary table. It's not really a "temporary table" in the Oracle sense of the word, but you can think about it that way to visualize what is going on.

If you are going to implement this solution for a regular audit run, I highly suggest you get the the "WITH" example to work or get QLEMO's solution to work. Like I said, the string concatenation solution I gave is really an ugly hack and my mother would probably wash my mouth out with soap if she knew I wrote it.

Thanks for the points.