Re: SQL Max question

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hello Art

If I may say back what I think you're saying -

You have a table with 1 row per DID# - that is, DID# is unique.

And status is not in this table.

And you want a result table that has all the columns from the first one, 
plus a status column that has the highest status for the DID# in that row.

OK, I think you are getting close and can get this with a JOIN and a 
nested table expression - you want the 2nd table to have the DID# and 
its highest status in it - then JOIN to that.

select table1.*, table2max.maxstatus from table1
  join (select did#, max(status) maxstatus from table2 group by did#) 
table2max
    on table1.did# = table2max.did#

Then you can do pretty much what you want - you could create a temp from 
this using CREATE TABLE, for example, and make it based on this SELECT 
statement.

HTH
Vern

On 9/5/2020 10:26 PM, Art Tostaine, Jr. wrote:
> There is one row in my first table keyed by did. Other tables are joined
> into this view but they are 1-1 by did. I joined it to a status file that
> has Did and 1-20 statuses/rows.
>
> In my new table I want all of the fields but only one row per did with the
> highest status.
>
> My goal is to create a temp table that I can run reports, extract to csv,
> etc.
>
> I wonder if I could create one row per did and then do an update with a
> select from the status file getting only the max row from it.
>
> On Sat, Sep 5, 2020 at 10:36 PM Alan Campin <alan0307d@xxxxxxxxx> wrote:
>
> > I am not completely understanding what you are doing here but normalizing
> >
> > table structure?
> >
> >
> >
> > Could you respond with what you see the table structure would look like?
> >
> >
> >
> > On Sat, Sep 5, 2020 at 6:52 PM Art Tostaine, Jr. <atostaine@xxxxxxxxx>
> >
> > wrote:
> >
> >
> >
> > > I have a table that has a DID#, many other columns, and a numeric status
> > > field.  I want the table to have all the columns and one row per did#
> > with
> >
> > > the highest status value.
> > > I'd like to either do a delete or create another temp table with only
> > these
> >
> > > records.  Is this possible?
> > > I've looked at group by but that won't let me keep all columns in the
> > > table.
> > > I also checked out  partition  by but that's not what I want.
> > > Thank you
> > > --
> > > Art Tostaine
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> >
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/midrange-l.
> > > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > > questions.
> > > Help support midrange.com by shopping at amazon.com with our affiliate
> > > link: https://amazon.midrange.com
> > --
> >
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> >
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> >
> > To subscribe, unsubscribe, or change list options,
> >
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> >
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> >
> > Before posting, please take a moment to review the archives
> >
> > at https://archive.midrange.com/midrange-l.
> >
> >
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > questions.
> >
> >
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate
> > link: https://amazon.midrange.com
> >
> > --
> Art Tostaine