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."

Mike Orr wrote:

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.

Sorry to disappoint you, but you won't learn a lot about subselects
and aggregates with that:

SELECT DISTINCT ON (loc_title) loc_title, id, loc_value, loc_unit
FROM mytable
ORDER BY loc_title;

Yours,
Laurenz Albe

This works beautifully. Thanks to you and Osvaldo; I learned something
more about querying today. I wasn't so much wanting to learn about
subqueries as to how to do these kinds of queries.

In this case, I'm testing a search routine, and I needed to extract
some possible results to expect. (I actually needed the 'name' column
too because that's what I'd input for the search, but I didn't realize
that until I got a working query and began testing. So I added the
name column and it worked.)

In other cases I've sometimes wanted to do a min or max but also get
additional information from the chosen rows. That's not quite this
case but it's an example of the kinds of queries I sometimes want to
do and then get stuck on, "Is this a case for a subquery or a window
or do I just need to use 'group by' more smartly? That's when I ask on
the list, to see what's the simplest way to do it all in one query.

On Wed, Sep 7, 2011 at 1:39 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

Mike Orr wrote:

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.

Sorry to disappoint you, but you won't learn a lot about subselects
and aggregates with that:

SELECT DISTINCT ON (loc_title) loc_title, id, loc_value, loc_unit
FROM mytable
ORDER BY loc_title;

Yours,
Laurenz Albe

--
Mike Orr <sluggoster@gmail.com>