Re: Complex query question

Hello,
> I have a complex query question whose answer I think would help me to
> understand subselects and aggregates better. I have a table with four
> columns of interest:
>
> id (int primary key), loc_title (varchar null), loc_value (float
> null), loc_unit (varchar null)
>
> I want the output columns to be:
> (1) each distinct value of loc_title, sorted
> (2) an id of a record containing that loc_title
> (3) the loc_value for the record in column 2
> (4) the loc_unit for the record in column 2
>
> I don't care as much how the records for columns 2-4 are chosen. It
> could be max(loc_value), min(id), or something else. I just need some
> sample records to test my program against.
>
> Is this something I should be able to do with a single query with a
> subselect, or is it too much for one query? I tried a few ways and
> none of them were syntactically valid.

Will this do?
test=# select * from myt;
id | loc_title | loc_value | loc_unit
----+-----------+-----------+----------
1 | AA | 80 | 10
2 | AA | 80 | 10
3 | BB | 80 | 10
4 | AA | 80 | 10
5 | BB | 80 | 10
(5 rows)

test=# select a.* from myt a where id in (select min(id) from myt group by
loc_title) order by loc_title;
id | loc_title | loc_value | loc_unit
----+-----------+-----------+----------
1 | AA | 80 | 10
3 | BB | 80 | 10
(2 rows)

Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."