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@xxxxxxxxxx> 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@xxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
