Thread: Complex query question
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. -- Mike Orr <sluggoster@gmail.com>
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."
> 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>