• [Info-ingres] Anyone care to try this query?

    From Roy Hann@21:1/5 to Ingres lists on Mon Apr 5 10:09:57 2021
    Either you worked it out for yourself or I don't understand the
    question.

    * create table allan ( col1 char(1) not null, col2 tinyint not null ) \g Executing . . .

    continue
    * insert into allan values ('A',1),('B',5),('A',2),('C',6),('C',7),('A',4),('D',8) \g
    Executing . . .

    (7 rows)
    continue
    * select col1,min(col2) from allan group by col1 order by col1 \g
    Executing . . .


    +------+------+
    |col1 |col2 |
    +------+------+
    |A | 1|
    |B | 5|
    |C | 6|
    |D | 8|
    +------+------+
    (4 rows)
    continue


    Roy

    Monday, April 5, 2021, 9:44:38 AM, you wrote:


    table1
    col1 col2
    A 1
    B 5
    A 2
    C 6
    C 7
    A 4
    D 8

    what I want to do is to select the following from the table above




    table 2 or selection
    col1 col2
    A 1
    B 5
    C 6
    D 8

    e.g. for col1 value A any of the col2 values for col1 = A , could be min , max it doesn't matter.

    I have tried lots of queries but cannot work it out.

    This is part of a series of sql queries.
    Obviously I could use an embedded 4GL or even create a suitable sql
    query using report writer but I'm sure this is possible as a single query.

    Any ideas?

    Thanks
    Allan












    Roy Hann
    Rational Commerce Ltd.
    T +44 20 8691 2089
    "Ingres development, tuning, and training experts"

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From aw@rationalcommerce.com@21:1/5 to Allan Biggs on Mon Apr 5 09:54:52 2021
    To: info-ingres@lists.planetingres.org (Ingres lists)

    Where col1 like '\[ABCD\]' escape '\'....For string with those letters in first place?------ Original message------From: Allan BiggsDate: Mon, 5 Apr 2021 09:44To: Ingres lists;Cc: Subject:[Info-ingres] Anyone care to try this query?





    table1
     


     
     


    col1
    col2


     
     


    A
    1


    B
    5


    A
    2


    C
    6


    C
    7


    A
    4


    D
    8




     

    what I want to do is to select the following from the table above

     

     

     








    table 2
    or selection 


     
     


    col1
    col2


     
     


    A
    1


    B
    5


    C
    6


    D
    8





     

    e.g. for col1 value A any of the col2 values for col1 = A , could be min , max it doesn't matter.

     

    I have tried lots of queries but cannot work it out.

     

    This is part of a series of sql queries.

    Obviously I could use an embedded 4GL or even create a suitable sql query using report writer but I'm sure this is
    possible as a single query.

     

    Any ideas?

     

    Thanks

    Allan

     

     

     

     

     

     

     

     

     
    <html><head>






    </head>
    <body>
    <div style="font-size: 10pt;"><div dir="auto">Where col1 like '\[ABCD\]' escape '\'....</div><div dir="auto"><br></div><div dir="auto">For string with those letters in first place?</div></div><div style="font-size: 10pt;"><div id="LGEmailHeader" dir="
    auto"><div dir="auto"><br></div><div dir="auto">------ Original message------</div><div dir="auto"><b>From: </b>Allan Biggs<allanb4@iname.com></allanb4@iname.com></div><div dir="auto"><b>Date: </b>Mon, 5 Apr 2021 09:44</div><div dir="auto"><b>To: </b>
    Ingres lists;</div><div dir="auto"><b>Cc: </b></div><div dir="auto"><b>Subject:</b>[Info-ingres] Anyone care to try this query?</div><div dir="auto"><br></div></div><div dir="auto"><div style="font-family: Verdana;font-size: 12.0px;"><table style="border-
    collapse:collapse; width:96pt; border:none" width="128">
    <colgroup>
    <col span="2" style="width:48pt" width="64">
    </colgroup>
    <tbody>
    <tr height="21" style="height:15.5pt">
    <td class="xl66"
    height="21" style="border:1.5pt solid windowtext; height:15.5pt; width:48pt; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap" width="64"><span style="font-size:11pt"><span style="color:
    black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">table1</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-left:none; width:48pt; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap" width="64"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66"
    height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><
    span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21"
    style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="
    font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">col1</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">col2</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt
    solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><
    span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext;
    height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-
    style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">A</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">1</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt;
    border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><
    span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">B</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">5</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none;
    text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-
    decoration:none"><span style="font-family:Calibri,sans-serif">A</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">2</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center;
    padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><
    span style="font-family:Calibri,sans-serif">C</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">6</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px;
    padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-
    family:Calibri,sans-serif">C</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">7</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px;
    padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-
    serif">A</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">4</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px;
    vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">D</span></
    span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">8</span></span></span></span></span></span></td>
    </tr>
    </tbody>
    </table>

    <div>&nbsp;</div>

    <div>what I want to do is to select the following from the table above</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>
    <table style="border-collapse:collapse; width:96pt; border:none"
    width="128">
    <colgroup>
    <col span="2" style="width:48pt" width="64">
    </colgroup>
    <tbody>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; width:48pt; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap" width="64"><span style="font-size:11pt"><
    span style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">table 2</span></span></span></span></span></span></td>
    <td class="xl67" style="border:1.5pt solid windowtext; border-left:none; width:48pt; text-align:center; white-space:nowrap; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom" width="64"><span style="font-size:11pt"><span
    style="font-weight:700"><span style="color:black"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">or selection&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">col1</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">col2</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">&nbsp;</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">A</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">1</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">B</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">5</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">C</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">6</span></span></span></span></span></span></td>
    </tr>
    <tr height="21" style="height:15.5pt">
    <td class="xl66" height="21" style="border:1.5pt solid windowtext; height:15.5pt; border-top:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span
    style="color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span style="font-family:Calibri,sans-serif">D</span></span></span></span></span></span></td>
    <td class="xl66" style="border:1.5pt solid windowtext; border-top:none; border-left:none; text-align:center; padding-top:1px; padding-right:1px; padding-left:1px; vertical-align:bottom; white-space:nowrap"><span style="font-size:11pt"><span style="
    color:black"><span style="font-weight:400"><span style="font-style:normal"><span style="text-decoration:none"><span
    style="font-family:Calibri,sans-serif">8</span></span></span></span></span></span></td>
    </tr>
    </tbody>
    </table>
    </div>

    <div>&nbsp;</div>

    <div>e.g. for col1 value A any of the col2 values for col1 = A , could be min , max it doesn't matter.</div>

    <div>&nbsp;</div>

    <div>I have tried lots of queries but cannot work it out.</div>

    <div>&nbsp;</div>

    <div>This is part of a series of sql queries.</div>

    <div>Obviously I could use an embedded 4GL or even create a suitable sql query using report writer but I'm sure this is possible as a single query.</div>

    <div>&nbsp;</div>

    <div>Any ideas?</div>

    <div>&nbsp;</div>

    <div>Thanks</div>

    <div>Allan</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div>

    <div>&nbsp;</div></div></div></div>


    </body></html>

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Karl Schendel@21:1/5 to Ingres lists on Mon Apr 5 09:07:32 2021
    On Apr 5, 2021, at 4:44 AM, Allan Biggs <allanb4@iname.com> wrote:


    e.g. for col1 value A any of the col2 values for col1 = A , could be min , max it doesn't matter.


    Sounds like you want a "first convenient value" function, which doesn't exist although I've been tempted a few times.

    The usual way is to use min or max instead.

    Karl

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From nikosv@21:1/5 to All on Mon Apr 5 13:35:34 2021
    WITH rowno AS (
    SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2 asc) AS
    row_number
    FROM allan
    )
    SELECT
    *
    FROM rowno
    WHERE row_number = 1

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)