Forum: >>> Magnum BBS <<<

[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"> </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"> </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"> </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"> </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"> </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> </div>

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

<div> </div>

<div> </div>

<div> </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 </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"> </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"> </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"> </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"> </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> </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> </div>

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

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

<div>Any ideas?</div>

<div> </div>

<div>Thanks</div>

<div>Allan</div>

<div> </div>

<div> </div>

<div> </div>

<div> </div>

<div> </div>

<div> </div>

<div> </div>

<div> </div>

<div> </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)

Who's Online
Recent Visitors
- Daniel Garrod
  Sat Feb 15 20:11:08 2025
  from Cambridge, Uk via Telnet
- Bob Worm
  Sat Feb 15 16:30:53 2025
  from Wales, Uk via Telnet
- Voyteck
  Sat Feb 15 16:28:13 2025
  from Poland via Telnet
- Bob Worm
  Sat Feb 15 14:25:51 2025
  from Wales, Uk via Telnet
- Plume
  Sat Feb 15 14:10:29 2025
  from Uk via Telnet
- Daniel Garrod
  Sat Feb 15 12:25:05 2025
  from Cambridge, Uk via Telnet
- Razorclad
  Sat Feb 15 06:23:43 2025
  from Denver, Co via Telnet
- Flatearthdisciple
  Sat Feb 15 04:25:14 2025
  from Moville, Ia via SSH

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	418
Nodes:	16 (2 / 14)
Uptime:	22:20:51
Calls:	8,765
Calls today:	8
Files:	13,287
D/L today:	3 files (1,633K bytes)
Messages:	5,964,658

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

Who's Online

Recent Visitors

System Info