From: | Mike Orr <sluggoster(at)gmail(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, pgsql-general(at)postgresql(dot)org, Osvaldo Kussama <osvaldo(dot)kussama(at)gmail(dot)com> |
Subject: | Re: Complex query question |
Date: | 2011-09-07 18:38:26 |
Message-ID: | CAH9f=ury=nvT8TPSGr7L8jyB5Ph1_3WPvq7yoCY9Jtq8USfM3w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)albe(at)wien(dot)gv(dot)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(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-09-07 18:43:19 | Re: [GENERAL] pg_upgrade problem |
Previous Message | Tom Lane | 2011-09-07 17:38:22 | Re: PL/pgSQL trigger and sequence increment |