Re: SQL Max question

Example with the Row_Number() ... as Kevin already said, you missed the
WHERE Condition

Example with the GROUP BY the JOIN clause is incomplete, you need to JOIN
NSStatus2 with MaxStatus.
The following Statement should work correctly.
With x as (Select SDDID#, Max(SNStatus2) MaxStatus
from WHSDTA.WHSSDOPT
Group by SDDID#)
select a.*
from WHSDTA.WHSSDOPT a join WHSDTA.WHSSN00 x
on a.SDDID# = x.SNDID# and a.NSStatus2 = x.MaxStatus
where sdentrydt Between '2020-01-01' and '2020-12-31'
and sdstatus <> 'V'
and sdmanbl# = 0
and sdmanif# = 0


Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Art
Tostaine, Jr.
Sent: Dienstag, 8. September 2020 16:41
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Max question

I tried both of Birgitta's these examples and they both returned multiple
records for each SDDID#. Here is the complete SQL's I tried:

With x as (Select a.*, Row_Number() Over(Partition by SDDID# Order By
SNStatus2 Desc) RowNbr
from WHSDTA.WHSSDOPT a
where sdentrydt >= '2020-01-01' and sdentrydt <= '2020-12-31' and sdstatus
<> 'V' and sdmanbl# = 0 and sdmanif# = 0)

Select *
from x

And:

With x as (Select SDDID#, Max(SNStatus2) MaxStatus
from WHSDTA.WHSSDOPT
Group by SDDID#)
select a.*
from WHSDTA.WHSSDOPT a join WHSDTA.WHSSN00 x on a.SDDID# = x.SNDID#
where sdentrydt >= '2020-01-01' and sdentrydt <= '2020-12-31' and sdstatus
<> 'V' and sdmanbl# = 0 and sdmanif# = 0;


On Sun, Sep 6, 2020 at 8:41 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

You can do it with a nested sub-select, but I prefer a Common Table
Expression

With x as (Select #DID. Max(Status) MaxStatus
from YourTable
Group by #DID)
select a.*
from YourTable a join x on a.#DID = x.#DID;

You can also can get the result by using the ROW_NUMBER() OLAP
Specification:

With x as (Select a.*, Row_Number() Over(Partition by #DID Order By
Status
Desc) RowNbr
from OrderDetx a)
Select *
from x
Where RowNbr = 1;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Vernon Hamberg
Sent: Sonntag, 6. September 2020 06:54
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Max question

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

--
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
--
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