Re: SQL Max question -- MIDRANGE-L

2nd one was wrong.

With x as (Select SDDID#, Max(SNStatus2) MaxStatus

from WHSDTA.WHSSDOPT

Group by SDDID#)

select a.*

from WHSDTA.WHSSDOPT a join x on a.SDDID# = x.SDDID#

where sdentrydt >= '2020-01-01' and sdentrydt <= '2020-12-31' and

sdstatus <> 'V' and sdmanbl# = 0 and sdmanif# = 0;

On Tue, 2020-09-08 at 14:48 +0000, Kevin Bucknum wrote:

You seem to be missing stuff in the first example - the where RowNbr = 1

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

where RowNbr = 1;

On the second one - you created a CTE, then don't use it? Maybe this

With x as (Select SDDID#, Max(SNStatus2) MaxStatus

from WHSDTA.WHSSDOPT

Group by SDDID#)

select a.*

from WHSDTA.WHSSDOPT a join x on a.SDDID# = x.MaxStatus

where sdentrydt >= '2020-01-01' and sdentrydt <= '2020-12-31' and

sdstatus <> 'V' and sdmanbl# = 0 and sdmanif# = 0;

On Tue, 2020-09-08 at 10:40 -0400, Art Tostaine, Jr. wrote:

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 <

<mailto:

<mailto:Hauser@xxxxxxxxxxxxxxx>

Hauser@xxxxxxxxxxxxxxx

<mailto:Hauser@xxxxxxxxxxxxxxx>

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 <

<mailto:

<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx

<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx

On Behalf Of

Vernon Hamberg

Sent: Sonntag, 6. September 2020 06:54

To: Midrange Systems Technical Discussion <

<mailto:

<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx

<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

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 <

<mailto:

<mailto:alan0307d@xxxxxxxxx>

alan0307d@xxxxxxxxx

<mailto:alan0307d@xxxxxxxxx>

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.

<

<mailto:

<mailto:atostaine@xxxxxxxxx>

atostaine@xxxxxxxxx

<mailto:atostaine@xxxxxxxxx>

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:

<mailto:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe,

unsubscribe, or change list options,

visit:

<

<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l

<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l

or email:

<mailto:

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at

<

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

.

Please contact

<mailto:

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

for any subscription

related questions.

Help support midrange.com by shopping at amazon.com with our

affiliate

link:

<

<https://amazon.midrange.com>

https://amazon.midrange.com

<https://amazon.midrange.com>

https://amazon.midrange.com

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list

To post a message email:

<mailto:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit:

<

<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives

at

<

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

.

Please contact

<mailto:

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

<https://amazon.midrange.com>

https://amazon.midrange.com

--

Art Tostaine

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:

<mailto:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

.

Please contact

<mailto:

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

<

<https://amazon.midrange.com>

https://amazon.midrange.com

<https://amazon.midrange.com>

https://amazon.midrange.com

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list

To post a message email:

<mailto:

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,

visit:

<

<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l

<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives

at

<

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

<https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l

.

Please contact

<mailto:

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx

for any subscription related

questions.

Help support midrange.com by shopping at amazon.com with our affiliate

link:

<

<https://amazon.midrange.com>

https://amazon.midrange.com

<https://amazon.midrange.com>

https://amazon.midrange.com

--

Art Tostaine

[

<https://www.medtronsoftware.com/img/MedtronMinilogo.bmp>

https://www.medtronsoftware.com/img/MedtronMinilogo.bmp

] Kevin Bucknum

Senior Programmer Analyst

MEDDATA / MEDTRON

120 Innwood Drive

Covington LA 70433

Local: 985-893-2550

Toll Free: 877-893-2550

<https://www.medtronsoftware.com>

https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.

[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com

CONFIDENTIALITY NOTICE

This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.