Thread: simple problem???

Dear users....
It's a simple problem I think (and I hope  :-)

I have a table like this:
A|B|C
1|2|20
1|3|10
1|4|21
2|3|12
2|4|22
3|4|23

where the first two column are a combination without repetition (with
k=2) of the numbers 1,2,3,4
for each pair I have a value in the column C.

I would want a select that can extract these records:

1|3|10
2|3|12
3|4|23


i.e. the select must look into the A field first and: 
-select all records where A=1, 
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields for this record.

then

-select all records where A=2
-find, into this selection, the record where there's the minimum value
of the field C 
-print all the fields of this record.

and so on...

using 
SELECT a,MIN(c) FROM table GROUP BY a

is a partial solution because I can't see the value of B
and I obtain:

1|10
2|12
3|23

How can I do for plotting also the value of B???

Thank you very much

Ivan





-- 
Ivan Marchesini
Department of Civil and Environmental Engineering
University of Perugia
Via G. Duranti 93/a 
06125
Perugia (Italy)
e-mail: marchesini@unipg.it       ivan.marchesini@gmail.com
tel: +39(0)755853760
fax: +39(0)755853756
jabber: geoivan73@jabber.org

> A|B|C
> 1|2|20
> 1|3|10
> 1|4|21
> 2|3|12
> 2|4|22
> 3|4|23
> 1|3|10
> 2|3|12
> 3|4|23
> -select all records where A=1, 
> -find, into this selection, the record where there's the minimum value
> of the field C 
> -print all the fields for this record.

maybe this will work;

select a,b,c
from table as T1
join (select a, min(c) as minc from table group by a) as T2
on (t1.a = t2.a) and (t1.c = t2.c)
;

Regards,

Richard Broersma Jr.

ivan marchesini wrote:

>Dear users....
>It's a simple problem I think (and I hope  :-)
>
>I have a table like this:
>A|B|C
>1|2|20
>1|3|10
>1|4|21
>2|3|12
>2|4|22
>3|4|23
>
>where the first two column are a combination without repetition (with
>k=2) of the numbers 1,2,3,4
>for each pair I have a value in the column C.
>
>I would want a select that can extract these records:
>
>1|3|10
>2|3|12
>3|4|23
>
>
>i.e. the select must look into the A field first and: 
>-select all records where A=1, 
>-find, into this selection, the record where there's the minimum value
>of the field C 
>-print all the fields for this record.
>
>then
>
>-select all records where A=2
>-find, into this selection, the record where there's the minimum value
>of the field C 
>-print all the fields of this record.
>
>and so on...
>
>using 
>SELECT a,MIN(c) FROM table GROUP BY a
>
>is a partial solution because I can't see the value of B
>and I obtain:
>
>1|10
>2|12
>3|23
>
>How can I do for plotting also the value of B???
>
>Thank you very much
>
>Ivan
>  
>
Maybe you could try
Select distinct on (a,b) a,b,c from(select * from table order by A,C)
The distinct on construct is a postgreSql-ism

Cordialement
-- 
Patrick