From Newsgroup: comp.databases.ingres

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"

--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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
--- Synchronet 3.21b-Linux NewsLink 1.2