Re: Subquery problems

#1

Nico,

select fld3 from table t where t.fld2 in (select max(fld2) from table);

HTH,

Johan Nel
Pretoria, South Africa.

Nico wrote:

Quote:

Hi everybody, this is probably a lame question so I apologize in advance.

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Thank you in advance.

#2

Nico,

select fld3 from table t where t.fld2 in (select max(fld2) from table);

HTH,

Johan Nel
Pretoria, South Africa.

Nico wrote:

Quote:

Hi everybody, this is probably a lame question so I apologize in advance.

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Thank you in advance.

#3

Nico <n8 (AT) langhirano (DOT) it> wrote:

Quote:

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

Do you mean 'GROUP BY Fld1'?

Quote:

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Joining with the same table is a sensible way.

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

-M-

#4

Nico <n8 (AT) langhirano (DOT) it> wrote:

Quote:

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

Do you mean 'GROUP BY Fld1'?

Quote:

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Joining with the same table is a sensible way.

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

-M-

#5

Nico <n8 (AT) langhirano (DOT) it> wrote:

Quote:

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

Do you mean 'GROUP BY Fld1'?

Quote:

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Joining with the same table is a sensible way.

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

-M-

#6

Nico <n8 (AT) langhirano (DOT) it> wrote:

Quote:

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

Do you mean 'GROUP BY Fld1'?

Quote:

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Joining with the same table is a sensible way.

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

-M-

#7

Nico <n8 (AT) langhirano (DOT) it> wrote:

Quote:

In a table like this:

Fld1, Fld2, Fld3

i launch a query like this:
SELECT MAX(Fld2) FROM table GROUP BY Fld2;
I would recover the value of Fld3 corresponding MAX(Fld2).

Do you mean 'GROUP BY Fld1'?

Quote:

In msAccess I would have done a sort of Fld2 and then a First(Fld3), but
I know this is not valid in pgsql. Are there smarter commands (perhaps a
subquery) other than doing a join with the same table?

Joining with the same table is a sensible way.

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

-M-

#8

Quote:

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

I did not know this clause, good to know, it could be really useful.

#9

Quote:

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

I did not know this clause, good to know, it could be really useful.

#10

Quote:

Another option is to use DISTINCT ON, so something like

SELECT DISTINCT ON (Fld1) Fld3 FROM table ORDER BY Fld1, Fld2 DESC;

But it's rather ugly. As usual, test with your own data to see how its
performance compares with other approaches.

I did not know this clause, good to know, it could be really useful.