Re: [SQL] pgsql aggregate: conditional max
> Hi, > > I need a special aggregation function. For instance, given the following > table data: > >aid| cat | weight > --+-+- > a1 | Drama | 1 > a1 | Romance | 6 > a1 | Short | 1 > a1 | Other | 7 > a2 | Comedy | 1 > a2 | Drama | 2 > a3 | Drama | 1 > a3 | Adult | 2 > a3 | Comedy | 1 > a3 | Other | 1 > > I want to group by "aid" and choose the category (i.e., "cat") with the > largest "weight": > > aid | max_weighted_cat > +- > a1 | Other > a2 | Drama > a3 | Adult > > Any ideas? Thank you! :) > SELECT aid, cat FROM table, ( SELECT aid, max(weight) as weight FROM table GROUP BY aid) AS tablemaxweight WHERE table.aid = tablemaxweight.aid AND table.weight = tablemaxweight.aid; There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid. -- Daniel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] pgsql aggregate: conditional max
Michael Fuhr wrote: On Sun, Mar 12, 2006 at 12:34:57AM -0500, Jeffrey Melloy wrote: Should be able to do this with a standard max() aggregate. select aid, cat, max(weight) from table group by aid, cat; That query returns the maximum weight for each (aid, cat) pair. Against the example data it returns the entire table, not the (aid, cat) pair with the max weight for a given aid. Michael is right. This query does not solve the problem... -- All best, Weimao Weimao Ke Indiana University Bloomington School of Library and Information Science http://ella.slis.indiana.edu/~wke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Question re: relational technique
Here is a sample table: item item_id int namechar attrib1 char attrib2 char attrib3 char One problem with the above is that the list of attributes is fixed. I am looking for a way to assign new, previously undefined, attributes to items without changing the table structure. Is it ever appropriate to do the following? item item_id int namechar details item_id int attribute_name char attribute_value char If this is a reasonable technique are their guidelines for its use? When is it approptiate? When not? Thanks, Bob ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pgsql aggregate: conditional max
Michael Fuhr wrote: SELECT DISTINCT ON (aid) aid, cat FROM tablename ORDER BY aid, weight DESC, cat; Good pointer. I think this will solve my problem. :) SELECT aid, cat FROM tablename AS t JOIN (SELECT aid, max(weight) AS weight FROM tablename GROUP BY aid) AS s USING (aid, weight); This query will return duplicates if there are multiple categories (for one aid) with the same max weight. Yet, I should be able to remove the duplicates somehow...:) I really appreciate your help! -- All best, Weimao Weimao Ke Indiana University Bloomington School of Library and Information Science http://ella.slis.indiana.edu/~wke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Question re: relational technique
--- Robert Paulsen <[EMAIL PROTECTED]> wrote: > One problem with the above is that the list of attributes is fixed. I > am looking for a way to assign new, previously undefined, attributes to > items without changing the table structure. Is it ever appropriate to do > the following? > ... There are two ways for extending tables, one static and one dynamic. Your scheme is dynamic. You will have problems with typing and performance. What you really want is to be able to list more attributes, similar to attributes attached to a tag in html. If you want a base table that has, under different specified conditions, extra attributes, there are better techniques. IMHO the best solution is, for each condition, create a table containing the primary table's id plus the extra attributes; then join that to the base table; then write a view to cover it all; then write rules for dml. Sounds difficult but a cake walk once you've done it a few times. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Question re: relational technique
On Sunday 12 March 2006 11:29, chester c young wrote: > --- Robert Paulsen <[EMAIL PROTECTED]> wrote: > > One problem with the above is that the list of attributes is fixed. I > > am looking for a way to assign new, previously undefined, attributes > > to > > > items without changing the table structure. Is it ever appropriate to > > do > > > the following? > > ... > > There are two ways for extending tables, one static and one dynamic. > > Your scheme is dynamic. You will have problems with typing and > performance. What you really want is to be able to list more > attributes, similar to attributes attached to a tag in html. > > If you want a base table that has, under different specified > conditions, extra attributes, there are better techniques. IMHO the > best solution is, for each condition, create a table containing the > primary table's id plus the extra attributes; then join that to the > base table So, to be sure I understand, something like ... item item_id int namechar cond_one cond_one_id int cond_one_descr char item_id int attribute_a char attribute_b int cond_two cond_two_id int cond_two_descr char item_id int attribute_c bool attribute_d date etc... This still requires me to modify the overall database structure but not the original item table. As my reward :) I get to use any type I choose for each new attribute. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pgsql aggregate: conditional max
Daniel CAUNE wrote: SELECT aid, cat FROM table, ( SELECT aid, max(weight) as weight FROM table GROUP BY aid) AS tablemaxweight WHERE table.aid = tablemaxweight.aid AND table.weight = tablemaxweight.aid; There is a limit case you don't specify how to deal with, when two or more categories have the same maximum weight. The query I wrote retrieves all the categories that have the maximum weight, but perhaps you just want one per aid. Yes, this will introduce duplicates. Yet it is not too difficult to select only one for each aid from the results. Thank you! Weimao -- Daniel -- All best, Weimao Weimao Ke Indiana University Bloomington School of Library and Information Science http://ella.slis.indiana.edu/~wke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Ask a PostgreSql question (about select )
Please post questions to the list. I'm forwarding this to the SQL list, as I think it's probably most applicable. I don't know much about the Oracle syntax you've used. Hopefully someone will be able to help you. On Mar 13, 2006, at 12:30 , min wrote: Please help me one PostgreSQL Statement, Thanks in Oracle select rownum,groupid,qty from abc --- --- 1 a5 3 2 a2 4 3 a3 5 4 5 . . . in PostgreSql How to wirte Statement ( Rownum -> change ??) [EMAIL PROTECTED] Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
