Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Take an astronaut to launch.


computers / comp.sys.tandem / Re: SQL Query usage of MAX for Date with a lot of registers

SubjectAuthor
* SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
`* Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
 +* Re: SQL Query usage of MAX for Date with a lot of registersRob Lesan
 |`* Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
 | `* Re: SQL Query usage of MAX for Date with a lot of registersJ G
 |  `* Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
 |   `* Re: SQL Query usage of MAX for Date with a lot of registersJ G
 |    +* Re: SQL Query usage of MAX for Date with a lot of registersRoberto Zucchinali
 |    |`- Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
 |    `- Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli
 `* Re: SQL Query usage of MAX for Date with a lot of registersJShepherd
  `- Re: SQL Query usage of MAX for Date with a lot of registersGustavo Bertazzoli

1
SQL Query usage of MAX for Date with a lot of registers

<73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=648&group=comp.sys.tandem#648

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ad4:5591:0:b0:626:461:1072 with SMTP id f17-20020ad45591000000b0062604611072mr188947qvx.5.1686116703647;
Tue, 06 Jun 2023 22:45:03 -0700 (PDT)
X-Received: by 2002:a81:7647:0:b0:565:dc02:9bfa with SMTP id
j7-20020a817647000000b00565dc029bfamr2267277ywk.5.1686116703275; Tue, 06 Jun
2023 22:45:03 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Tue, 6 Jun 2023 22:45:02 -0700 (PDT)
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.35; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.35
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
Subject: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Wed, 07 Jun 2023 05:45:03 +0000
Content-Type: text/plain; charset="UTF-8"
 by: Gustavo Bertazzoli - Wed, 7 Jun 2023 05:45 UTC

Hello guys,

Yes, I'm here again asking for SQL help. :)

Basically, I have a table with the following fields:
ID PIC 9(7) DEFAULT SYSTEM NOT NULL
POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL

None of them are unique. This means that ID could appear several times for several different points and in different timestamps.

The result that I need is
ID,POINT,TIMESTAMP
But I just want the last one. With last one I mean just the last time that when an ID, reach a point, based on the timestamp, it's possible but none of my queries are really working as I expect.

Here is one example:
select
S1.ID,
S1.POINT,
S1.TIMESTAMP
from
=information S1
where
S1.ID between 7000000 and 8999999
AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
where
S2.ID between 7000000 and 8999999 browse access
)
browse access;

With the query above, when I try with only one ID instead of range (in between clause), it works. But with this range, the query is running for more than an hour and there is still no result, so I believe something is not right.

Can you please help me with that? Or better what I'm doing wrong?

Thanks a lot in advance and greetings from China!

Re: SQL Query usage of MAX for Date with a lot of registers

<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=649&group=comp.sys.tandem#649

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ac8:5804:0:b0:3f3:64cd:7c60 with SMTP id g4-20020ac85804000000b003f364cd7c60mr606548qtg.3.1686116982916;
Tue, 06 Jun 2023 22:49:42 -0700 (PDT)
X-Received: by 2002:a81:451f:0:b0:561:b4ff:4303 with SMTP id
s31-20020a81451f000000b00561b4ff4303mr2292875ywa.5.1686116982694; Tue, 06 Jun
2023 22:49:42 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Tue, 6 Jun 2023 22:49:42 -0700 (PDT)
In-Reply-To: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.35; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.35
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Wed, 07 Jun 2023 05:49:42 +0000
Content-Type: text/plain; charset="UTF-8"
X-Received-Bytes: 2740
 by: Gustavo Bertazzoli - Wed, 7 Jun 2023 05:49 UTC

On Wednesday, 7 June 2023 at 13:45:04 UTC+8, Gustavo Bertazzoli wrote:
> Hello guys,
>
> Yes, I'm here again asking for SQL help. :)
>
> Basically, I have a table with the following fields:
> ID PIC 9(7) DEFAULT SYSTEM NOT NULL
> POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
> TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL
>
> None of them are unique. This means that ID could appear several times for several different points and in different timestamps.
>
> The result that I need is
> ID,POINT,TIMESTAMP
> But I just want the last one. With last one I mean just the last time that when an ID, reach a point, based on the timestamp, it's possible but none of my queries are really working as I expect.
>
> Here is one example:
> select
> S1.ID,
> S1.POINT,
> S1.TIMESTAMP
> from
> =information S1
> where
> S1.ID between 7000000 and 8999999
> AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
> where
> S2.ID between 7000000 and 8999999 browse access
> )
> browse access;
>
> With the query above, when I try with only one ID instead of range (in between clause), it works. But with this range, the query is running for more than an hour and there is still no result, so I believe something is not right.
>
> Can you please help me with that? Or better what I'm doing wrong?
>
> Thanks a lot in advance and greetings from China!

Just got the result and it's pretty bad. The output is just one line and not all IDs(in the range) with latest timestamp for the last point.

So, any clue how can I achieve what I need?

Re: SQL Query usage of MAX for Date with a lot of registers

<f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=650&group=comp.sys.tandem#650

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ad4:4f82:0:b0:625:971a:7788 with SMTP id em2-20020ad44f82000000b00625971a7788mr473145qvb.0.1686164917739;
Wed, 07 Jun 2023 12:08:37 -0700 (PDT)
X-Received: by 2002:a81:b148:0:b0:565:b7d6:712d with SMTP id
p69-20020a81b148000000b00565b7d6712dmr3571945ywh.2.1686164917538; Wed, 07 Jun
2023 12:08:37 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer03.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 7 Jun 2023 12:08:37 -0700 (PDT)
In-Reply-To: <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=96.241.1.170; posting-account=aJvDVgoAAAButCoTm3t94f2FeCuWCxki
NNTP-Posting-Host: 96.241.1.170
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com> <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: rob.le...@xypro.com (Rob Lesan)
Injection-Date: Wed, 07 Jun 2023 19:08:37 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 3232
 by: Rob Lesan - Wed, 7 Jun 2023 19:08 UTC

On Wednesday, June 7, 2023 at 1:49:43 AM UTC-4, Gustavo Bertazzoli wrote:
> On Wednesday, 7 June 2023 at 13:45:04 UTC+8, Gustavo Bertazzoli wrote:
> > Hello guys,
> >
> > Yes, I'm here again asking for SQL help. :)
> >
> > Basically, I have a table with the following fields:
> > ID PIC 9(7) DEFAULT SYSTEM NOT NULL
> > POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
> > TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL
> >
> > None of them are unique. This means that ID could appear several times for several different points and in different timestamps.
> >
> > The result that I need is
> > ID,POINT,TIMESTAMP
> > But I just want the last one. With last one I mean just the last time that when an ID, reach a point, based on the timestamp, it's possible but none of my queries are really working as I expect.
> >
> > Here is one example:
> > select
> > S1.ID,
> > S1.POINT,
> > S1.TIMESTAMP
> > from
> > =information S1
> > where
> > S1.ID between 7000000 and 8999999
> > AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
> > where
> > S2.ID between 7000000 and 8999999 browse access
> > )
> > browse access;
> >
> > With the query above, when I try with only one ID instead of range (in between clause), it works. But with this range, the query is running for more than an hour and there is still no result, so I believe something is not right.
> >
> > Can you please help me with that? Or better what I'm doing wrong?
> >
> > Thanks a lot in advance and greetings from China!
> Just got the result and it's pretty bad. The output is just one line and not all IDs(in the range) with latest timestamp for the last point.
>
> So, any clue how can I achieve what I need?

How large is the table? When was the last time the statistics where updated?

Have you run an explain plain on this query?

Re: SQL Query usage of MAX for Date with a lot of registers

<u5qsu8$182hc$1@dont-email.me>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=651&group=comp.sys.tandem#651

 copy link   Newsgroups: comp.sys.tandem
Path: i2pn2.org!i2pn.org!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: inva...@nowhere.com (JShepherd)
Newsgroups: comp.sys.tandem
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
Date: Wed, 7 Jun 2023 21:30:49 -0000 (UTC)
Organization: A noiseless patient Spider
Lines: 75
Message-ID: <u5qsu8$182hc$1@dont-email.me>
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com> <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>
MIME-Version: 1.0
Content-Type: Text/Plain; charset=US-ASCII
Injection-Date: Wed, 7 Jun 2023 21:30:49 -0000 (UTC)
Injection-Info: dont-email.me; posting-host="040abaca89730fa2f52fef7264a96bc9";
logging-data="1313324"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+sfa2EQ3uKCrGyhi+HNA38jJ2KX7SzG/c="
Cancel-Lock: sha1:zyMoz3wqn9P635J9KIx0Cp2raHU=
 by: JShepherd - Wed, 7 Jun 2023 21:30 UTC

In article <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com>,
guberta@gmail.com says...
>
>On Wednesday, 7 June 2023 at 13:45:04 UTC+8, Gustavo Bertazzoli wrote:
>> Hello guys,
>>
>> Yes, I'm here again asking for SQL help. :)
>>
>> Basically, I have a table with the following fields:
>> ID PIC 9(7) DEFAULT SYSTEM NOT NULL
>> POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
>> TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL
>>
>> None of them are unique. This means that ID could appear several times for
sev
>eral different points and in different timestamps.
>>
>> The result that I need is
>> ID,POINT,TIMESTAMP
>> But I just want the last one. With last one I mean just the last time that
whe
>n an ID, reach a point, based on the timestamp, it's possible but none of my
que
>ries are really working as I expect.
>>
>> Here is one example:
>> select
>> S1.ID,
>> S1.POINT,
>> S1.TIMESTAMP
>> from
>> =information S1
>> where
>> S1.ID between 7000000 and 8999999
>> AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
>> where
>> S2.ID between 7000000 and 8999999 browse access
>> )
>> browse access;
>>
>> With the query above, when I try with only one ID instead of range (in
between
> clause), it works. But with this range, the query is running for more than
an h
>our and there is still no result, so I believe something is not right.
>>
>> Can you please help me with that? Or better what I'm doing wrong?
>>
>> Thanks a lot in advance and greetings from China!
>
>Just got the result and it's pretty bad. The output is just one line and not
all
> IDs(in the range) with latest timestamp for the last point.
>
>So, any clue how can I achieve what I need?

Are you saying that, given a set of rows like this
you want only the two starred rows returned ?

ID POINT TIMESTAMP
------- -------- --------------------
7000000 10 1
7000000 10 2
7000000 11 3
7000000 11 4
*7000000 11 5
7000002 2 21
7000002 3 22
7000002 4 23
7000002 5 24
*7000002 6 25

Re: SQL Query usage of MAX for Date with a lot of registers

<ee9ca30c-5195-4982-bf44-25dca805662en@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=652&group=comp.sys.tandem#652

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ac8:5ed7:0:b0:3f6:b049:3941 with SMTP id s23-20020ac85ed7000000b003f6b0493941mr119791qtx.1.1686186107965;
Wed, 07 Jun 2023 18:01:47 -0700 (PDT)
X-Received: by 2002:a25:9d90:0:b0:bb3:8945:d6fa with SMTP id
v16-20020a259d90000000b00bb38945d6famr2662329ybp.7.1686186107505; Wed, 07 Jun
2023 18:01:47 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 7 Jun 2023 18:01:47 -0700 (PDT)
In-Reply-To: <u5qsu8$182hc$1@dont-email.me>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.35; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.35
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <u5qsu8$182hc$1@dont-email.me>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <ee9ca30c-5195-4982-bf44-25dca805662en@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Thu, 08 Jun 2023 01:01:47 +0000
Content-Type: text/plain; charset="UTF-8"
X-Received-Bytes: 3623
 by: Gustavo Bertazzoli - Thu, 8 Jun 2023 01:01 UTC

On Thursday, 8 June 2023 at 05:30:52 UTC+8, JShepherd wrote:
> In article <64ab14cb-528c-4d9e...@googlegroups.com>,
> gub...@gmail.com says...
> >
> >On Wednesday, 7 June 2023 at 13:45:04 UTC+8, Gustavo Bertazzoli wrote:
> >> Hello guys,
> >>
> >> Yes, I'm here again asking for SQL help. :)
> >>
> >> Basically, I have a table with the following fields:
> >> ID PIC 9(7) DEFAULT SYSTEM NOT NULL
> >> POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
> >> TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL
> >>
> >> None of them are unique. This means that ID could appear several times for
> sev
> >eral different points and in different timestamps.
> >>
> >> The result that I need is
> >> ID,POINT,TIMESTAMP
> >> But I just want the last one. With last one I mean just the last time that
> whe
> >n an ID, reach a point, based on the timestamp, it's possible but none of my
> que
> >ries are really working as I expect.
> >>
> >> Here is one example:
> >> select
> >> S1.ID,
> >> S1.POINT,
> >> S1.TIMESTAMP
> >> from
> >> =information S1
> >> where
> >> S1.ID between 7000000 and 8999999
> >> AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
> >> where
> >> S2.ID between 7000000 and 8999999 browse access
> >> )
> >> browse access;
> >>
> >> With the query above, when I try with only one ID instead of range (in
> between
> > clause), it works. But with this range, the query is running for more than
> an h
> >our and there is still no result, so I believe something is not right.
> >>
> >> Can you please help me with that? Or better what I'm doing wrong?
> >>
> >> Thanks a lot in advance and greetings from China!
> >
> >Just got the result and it's pretty bad. The output is just one line and not
> all
> > IDs(in the range) with latest timestamp for the last point.
> >
> >So, any clue how can I achieve what I need?
> Are you saying that, given a set of rows like this
> you want only the two starred rows returned ?
>
>
> ID POINT TIMESTAMP
> ------- -------- --------------------
> 7000000 10 1
> 7000000 10 2
> 7000000 11 3
> 7000000 11 4
> *7000000 11 5
> 7000002 2 21
> 7000002 3 22
> 7000002 4 23
> 7000002 5 24
> *7000002 6 25

Hi JShepard,
This is exactly what I'm looking for, because the 7000000 11 5 have the biggest timestamp and also the 7000002.

Re: SQL Query usage of MAX for Date with a lot of registers

<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=653&group=comp.sys.tandem#653

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:ac8:4e8f:0:b0:3f6:a725:25ad with SMTP id 15-20020ac84e8f000000b003f6a72525admr1462291qtp.5.1686191115950;
Wed, 07 Jun 2023 19:25:15 -0700 (PDT)
X-Received: by 2002:a81:b724:0:b0:561:8c16:2b66 with SMTP id
v36-20020a81b724000000b005618c162b66mr514739ywh.4.1686191115541; Wed, 07 Jun
2023 19:25:15 -0700 (PDT)
Path: i2pn2.org!i2pn.org!news.1d4.us!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Wed, 7 Jun 2023 19:25:15 -0700 (PDT)
In-Reply-To: <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.35; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.35
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Thu, 08 Jun 2023 02:25:15 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 3916
 by: Gustavo Bertazzoli - Thu, 8 Jun 2023 02:25 UTC

On Thursday, 8 June 2023 at 03:08:39 UTC+8, Rob Lesan wrote:
> On Wednesday, June 7, 2023 at 1:49:43 AM UTC-4, Gustavo Bertazzoli wrote:
> > On Wednesday, 7 June 2023 at 13:45:04 UTC+8, Gustavo Bertazzoli wrote:
> > > Hello guys,
> > >
> > > Yes, I'm here again asking for SQL help. :)
> > >
> > > Basically, I have a table with the following fields:
> > > ID PIC 9(7) DEFAULT SYSTEM NOT NULL
> > > POINT PIC 9(8) DEFAULT SYSTEM NOT NULL
> > > TIMESTAMP LARGEINT DEFAULT SYSTEM NOT NULL
> > >
> > > None of them are unique. This means that ID could appear several times for several different points and in different timestamps.
> > >
> > > The result that I need is
> > > ID,POINT,TIMESTAMP
> > > But I just want the last one. With last one I mean just the last time that when an ID, reach a point, based on the timestamp, it's possible but none of my queries are really working as I expect.
> > >
> > > Here is one example:
> > > select
> > > S1.ID,
> > > S1.POINT,
> > > S1.TIMESTAMP
> > > from
> > > =information S1
> > > where
> > > S1.ID between 7000000 and 8999999
> > > AND S1.TIMESTAMP = (select MAX(TIMESTAMP) from =information S2
> > > where
> > > S2.ID between 7000000 and 8999999 browse access
> > > )
> > > browse access;
> > >
> > > With the query above, when I try with only one ID instead of range (in between clause), it works. But with this range, the query is running for more than an hour and there is still no result, so I believe something is not right.
> > >
> > > Can you please help me with that? Or better what I'm doing wrong?
> > >
> > > Thanks a lot in advance and greetings from China!
> > Just got the result and it's pretty bad. The output is just one line and not all IDs(in the range) with latest timestamp for the last point.
> >
> > So, any clue how can I achieve what I need?
> How large is the table? When was the last time the statistics where updated?
>
> Have you run an explain plain on this query?

Hi Rob,

The table is pretty big, I believe something around 1 milion registers or even more, in a fee minutes I can check.
The statistics are updated every 6 months, but it’s pretty accurate because the size of the table is always in average the same due to the business process behind.

Just got the results and actually the table have:
(EXPR)
--------------------

30818846

Much more then I thought.

Re: SQL Query usage of MAX for Date with a lot of registers

<20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=654&group=comp.sys.tandem#654

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a05:620a:28d0:b0:75c:b18b:712f with SMTP id l16-20020a05620a28d000b0075cb18b712fmr1896615qkp.7.1686262401306;
Thu, 08 Jun 2023 15:13:21 -0700 (PDT)
X-Received: by 2002:a81:b70d:0:b0:561:9091:ff91 with SMTP id
v13-20020a81b70d000000b005619091ff91mr560686ywh.2.1686262400942; Thu, 08 Jun
2023 15:13:20 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!1.us.feeder.erje.net!feeder.erje.net!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Thu, 8 Jun 2023 15:13:20 -0700 (PDT)
In-Reply-To: <4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=82.13.148.46; posting-account=wnnoXgoAAACl5lRfMyHu7dTvTggl5H62
NNTP-Posting-Host: 82.13.148.46
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: goodbod...@gmail.com (J G)
Injection-Date: Thu, 08 Jun 2023 22:13:21 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 2426
 by: J G - Thu, 8 Jun 2023 22:13 UTC

NonStop SQL isn't really my thing (so maybe I'm not the best person to respond!), but does this work at a functional level?:

select
S1.ID,
S1.POINT,
max(S1.TIMESTAMP)
from
=information S1
where
S1.ID between 7000000 and 8999999
group by S1.ID
browse access;

I don't have NonStop access to test that, so it's possible NonStop SQL won't like it!

When it comes to performance clearly you want to avoid a scan of all rows in the table. A composite index of ID and TIMESTAMP may allow the optimizer to skip scan down the index, read just one index entry for each unique ID in the range and then jump across with one read of the main table for each index row read.

If you don't have an index leading with ID and TIMESTAMP then a full table scan may be unavoidable. Even with it I don't know if the optimizer will actually do a skip scan on NonStop.

Maybe experiment with a copy of the table with just a few thousand rows and updated stats, look at the query plan and see if any of this is of any help.

Good luck!

Re: SQL Query usage of MAX for Date with a lot of registers

<55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=655&group=comp.sys.tandem#655

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a05:6214:4a47:b0:626:65b7:84f with SMTP id ph7-20020a0562144a4700b0062665b7084fmr219518qvb.9.1686299214612;
Fri, 09 Jun 2023 01:26:54 -0700 (PDT)
X-Received: by 2002:a25:d812:0:b0:ba6:af30:21c2 with SMTP id
p18-20020a25d812000000b00ba6af3021c2mr455093ybg.6.1686299214283; Fri, 09 Jun
2023 01:26:54 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Fri, 9 Jun 2023 01:26:53 -0700 (PDT)
In-Reply-To: <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.35; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.35
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com> <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Fri, 09 Jun 2023 08:26:54 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
 by: Gustavo Bertazzoli - Fri, 9 Jun 2023 08:26 UTC

On Friday, 9 June 2023 at 06:13:22 UTC+8, J G wrote:
> NonStop SQL isn't really my thing (so maybe I'm not the best person to respond!), but does this work at a functional level?:
>
> select
> S1.ID,
> S1.POINT,
> max(S1.TIMESTAMP)
> from
> =information S1
> where
> S1.ID between 7000000 and 8999999
> group by S1.ID
> browse access;
>
> I don't have NonStop access to test that, so it's possible NonStop SQL won't like it!
>
> When it comes to performance clearly you want to avoid a scan of all rows in the table. A composite index of ID and TIMESTAMP may allow the optimizer to skip scan down the index, read just one index entry for each unique ID in the range and then jump across with one read of the main table for each index row read.
>
> If you don't have an index leading with ID and TIMESTAMP then a full table scan may be unavoidable. Even with it I don't know if the optimizer will actually do a skip scan on NonStop.
>
> Maybe experiment with a copy of the table with just a few thousand rows and updated stats, look at the query plan and see if any of this is of any help.
>
> Good luck!

Hello JG,

Thanks for the suggestions, but unfortunately this idea I already had before and it doesn't return unique IDs. The result is very similar of JShepherd's list.
Any other suggestions?

Re: SQL Query usage of MAX for Date with a lot of registers

<3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=656&group=comp.sys.tandem#656

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a37:648:0:b0:75e:bb25:e0bd with SMTP id 69-20020a370648000000b0075ebb25e0bdmr268730qkg.6.1686318137470;
Fri, 09 Jun 2023 06:42:17 -0700 (PDT)
X-Received: by 2002:a05:6902:1085:b0:bac:f582:ef18 with SMTP id
v5-20020a056902108500b00bacf582ef18mr808126ybu.5.1686318137064; Fri, 09 Jun
2023 06:42:17 -0700 (PDT)
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Fri, 9 Jun 2023 06:42:16 -0700 (PDT)
In-Reply-To: <55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=2a0e:d784:8678:f000:264:6b0e:44a8:71b1;
posting-account=wnnoXgoAAACl5lRfMyHu7dTvTggl5H62
NNTP-Posting-Host: 2a0e:d784:8678:f000:264:6b0e:44a8:71b1
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com> <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
<55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: goodbod...@gmail.com (J G)
Injection-Date: Fri, 09 Jun 2023 13:42:17 +0000
Content-Type: text/plain; charset="UTF-8"
 by: J G - Fri, 9 Jun 2023 13:42 UTC

How about:

select
S1.ID,
S1.POINT,
S1.TIMESTAMP
from
=information S1
INNER JOIN (
SELECT ID, MAX(TIMESTAMP) TIMESTAMP
FROM =information
GROUP BY ID
) S2 ON S1.id = S2.id AND S1.TIMESTAMP = S2.TIMESTAMP
browse access;

I've run this on a MySQL database (without the DEFINEs and browse access) and with JShepherd's data above and it returns:

ID POINT TIMESTAMP
7000000 11 5
7000002 6 25

I'm not sure about performance but let's see if we can get the right result set first!

Re: SQL Query usage of MAX for Date with a lot of registers

<4f7806b0-4b4b-44d0-8bd8-895c2cb6fb9bn@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=657&group=comp.sys.tandem#657

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a37:c06:0:b0:75c:b18b:712f with SMTP id 6-20020a370c06000000b0075cb18b712fmr1622179qkm.7.1686613179993; Mon, 12 Jun 2023 16:39:39 -0700 (PDT)
X-Received: by 2002:a81:bd0d:0:b0:565:dc02:9bfa with SMTP id b13-20020a81bd0d000000b00565dc029bfamr102786ywi.5.1686613179572; Mon, 12 Jun 2023 16:39:39 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!feeder.usenetexpress.com!tr1.iad1.usenetexpress.com!69.80.99.18.MISMATCH!border-1.nntp.ord.giganews.com!nntp.giganews.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Mon, 12 Jun 2023 16:39:39 -0700 (PDT)
In-Reply-To: <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=79.17.79.123; posting-account=HYX4_AoAAACRB72z46rzQC1mpbzyBGdB
NNTP-Posting-Host: 79.17.79.123
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com> <64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com> <4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com> <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com> <55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com> <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <4f7806b0-4b4b-44d0-8bd8-895c2cb6fb9bn@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: roberto....@gmail.com (Roberto Zucchinali)
Injection-Date: Mon, 12 Jun 2023 23:39:39 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Lines: 53
 by: Roberto Zucchinali - Mon, 12 Jun 2023 23:39 UTC

On Friday, June 9, 2023 at 3:42:18 PM UTC+2, J G wrote:
> How about:
> select
> S1.ID,
> S1.POINT,
> S1.TIMESTAMP
> from
> =information S1
> INNER JOIN (
> SELECT ID, MAX(TIMESTAMP) TIMESTAMP
> FROM =information
> GROUP BY ID
> ) S2 ON S1.id = S2.id AND S1.TIMESTAMP = S2.TIMESTAMP
> browse access;
>
> I've run this on a MySQL database (without the DEFINEs and browse access) and with JShepherd's data above and it returns:
>
> ID POINT TIMESTAMP
> 7000000 11 5
> 7000002 6 25
>
> I'm not sure about performance but let's see if we can get the right result set first!

Hi Gustavo,
I changed the WHERE clause in your original subquery

This is my proposal:
select
S1.ID,
S1.POINT,
S1.TIMESTAMP
from
=information S1
where
S1.ID between 7000000 and 8999999
AND S1.TIMESTAMP = (select MAX(S2.TIMESTAMP) from =information S2
where
S2.ID = S1.ID browse access)
browse access ;

In order to be able to suggest any changes to optimize the performance of the query, it is mandatory to know the structure of the table (“INVOKE =information1“ from SQLCI) and also, the primary key and the indexes of the table ("FILEINFO =information1, DETAIL" from SQLCI) .

Other steps that can improve performance include:
- The reload of the table and/or indexes to reclaim unused space
- new table and/or index partitions

Re: SQL Query usage of MAX for Date with a lot of registers

<37847c81-6151-49c0-823d-9104442b0c8an@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=658&group=comp.sys.tandem#658

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a37:4186:0:b0:74e:437f:e942 with SMTP id o128-20020a374186000000b0074e437fe942mr984665qka.8.1686624323667;
Mon, 12 Jun 2023 19:45:23 -0700 (PDT)
X-Received: by 2002:a25:15c4:0:b0:bc5:2869:d735 with SMTP id
187-20020a2515c4000000b00bc52869d735mr135635ybv.13.1686624323314; Mon, 12 Jun
2023 19:45:23 -0700 (PDT)
Path: i2pn2.org!i2pn.org!usenet.blueworldhosting.com!diablo1.usenet.blueworldhosting.com!peer01.iad!feed-me.highwinds-media.com!news.highwinds-media.com!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Mon, 12 Jun 2023 19:45:22 -0700 (PDT)
In-Reply-To: <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.34; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.34
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com> <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
<55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com> <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <37847c81-6151-49c0-823d-9104442b0c8an@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Tue, 13 Jun 2023 02:45:23 +0000
Content-Type: text/plain; charset="UTF-8"
X-Received-Bytes: 2242
 by: Gustavo Bertazzoli - Tue, 13 Jun 2023 02:45 UTC

On Friday, 9 June 2023 at 21:42:18 UTC+8, J G wrote:
> How about:
> select
> S1.ID,
> S1.POINT,
> S1.TIMESTAMP
> from
> =information S1
> INNER JOIN (
> SELECT ID, MAX(TIMESTAMP) TIMESTAMP
> FROM =information
> GROUP BY ID
> ) S2 ON S1.id = S2.id AND S1.TIMESTAMP = S2.TIMESTAMP
> browse access;
>
> I've run this on a MySQL database (without the DEFINEs and browse access) and with JShepherd's data above and it returns:
>
> ID POINT TIMESTAMP
> 7000000 11 5
> 7000002 6 25
>
> I'm not sure about performance but let's see if we can get the right result set first!

In SQL/MP it didn't work, I have to change it to adapt a bit. But it took 2 hours and in the end I got an error of too long execution. =[

Re: SQL Query usage of MAX for Date with a lot of registers

<76afcdde-31c3-4582-886c-a75afdb48afbn@googlegroups.com>

 copy mid

https://www.novabbs.com/computers/article-flat.php?id=659&group=comp.sys.tandem#659

 copy link   Newsgroups: comp.sys.tandem
X-Received: by 2002:a37:9a10:0:b0:75c:9ab6:4381 with SMTP id c16-20020a379a10000000b0075c9ab64381mr1727414qke.10.1686624487719;
Mon, 12 Jun 2023 19:48:07 -0700 (PDT)
X-Received: by 2002:a25:c6:0:b0:bc4:8627:57c3 with SMTP id 189-20020a2500c6000000b00bc4862757c3mr142720yba.9.1686624487277;
Mon, 12 Jun 2023 19:48:07 -0700 (PDT)
Path: i2pn2.org!i2pn.org!news.niel.me!glou.org!news.glou.org!usenet-fr.net!proxad.net!feeder1-2.proxad.net!209.85.160.216.MISMATCH!news-out.google.com!nntp.google.com!postnews.google.com!google-groups.googlegroups.com!not-for-mail
Newsgroups: comp.sys.tandem
Date: Mon, 12 Jun 2023 19:48:06 -0700 (PDT)
In-Reply-To: <4f7806b0-4b4b-44d0-8bd8-895c2cb6fb9bn@googlegroups.com>
Injection-Info: google-groups.googlegroups.com; posting-host=141.113.3.34; posting-account=y6cHAAoAAAC6eCczfLV2Rx_5bZz07tIn
NNTP-Posting-Host: 141.113.3.34
References: <73114343-0e45-4f0c-bd0a-29192310fae8n@googlegroups.com>
<64ab14cb-528c-4d9e-8aff-d9600c4b2b17n@googlegroups.com> <f4037ef8-8502-4dac-aaa7-f769a2070744n@googlegroups.com>
<4bb13be2-6642-43e9-ab77-64c749af99c4n@googlegroups.com> <20b4eec9-6e82-4fe5-93ff-47be0df06341n@googlegroups.com>
<55da8ed4-de71-4b12-a1fe-10b1c6f5356dn@googlegroups.com> <3d811ed5-de12-400f-9c5a-27fec3e54cbcn@googlegroups.com>
<4f7806b0-4b4b-44d0-8bd8-895c2cb6fb9bn@googlegroups.com>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <76afcdde-31c3-4582-886c-a75afdb48afbn@googlegroups.com>
Subject: Re: SQL Query usage of MAX for Date with a lot of registers
From: gube...@gmail.com (Gustavo Bertazzoli)
Injection-Date: Tue, 13 Jun 2023 02:48:07 +0000
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
 by: Gustavo Bertazzoli - Tue, 13 Jun 2023 02:48 UTC

On Tuesday, 13 June 2023 at 07:39:40 UTC+8, Roberto Zucchinali wrote:
> On Friday, June 9, 2023 at 3:42:18 PM UTC+2, J G wrote:
> > How about:
> > select
> > S1.ID,
> > S1.POINT,
> > S1.TIMESTAMP
> > from
> > =information S1
> > INNER JOIN (
> > SELECT ID, MAX(TIMESTAMP) TIMESTAMP
> > FROM =information
> > GROUP BY ID
> > ) S2 ON S1.id = S2.id AND S1.TIMESTAMP = S2.TIMESTAMP
> > browse access;
> >
> > I've run this on a MySQL database (without the DEFINEs and browse access) and with JShepherd's data above and it returns:
> >
> > ID POINT TIMESTAMP
> > 7000000 11 5
> > 7000002 6 25
> >
> > I'm not sure about performance but let's see if we can get the right result set first!
> Hi Gustavo,
> I changed the WHERE clause in your original subquery
>
> This is my proposal:
> select
> S1.ID,
> S1.POINT,
> S1.TIMESTAMP
> from
> =information S1
> where
> S1.ID between 7000000 and 8999999
> AND S1.TIMESTAMP = (select MAX(S2.TIMESTAMP) from =information S2
> where
> S2.ID = S1.ID browse access)
> browse access ;
>
>
> In order to be able to suggest any changes to optimize the performance of the query, it is mandatory to know the structure of the table (“INVOKE =information1“ from SQLCI) and also, the primary key and the indexes of the table ("FILEINFO =information1, DETAIL" from SQLCI) .
>
>
> Other steps that can improve performance include:
> - The reload of the table and/or indexes to reclaim unused space
> - new table and/or index partitions

WOW Roberto! That's perfect! Thanks a lot!

It worked and actually the result came pretty fast!

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor