Re: [GENERAL] Group By Question
That's getting too complicated. It can be done simply as: SELECT DISTINCT(test.people.id) test.people.id, test.people.name, test.likes.ref FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref ORDER BY test.people.id, test.likes.date DESCENDING Assuming people.id is unique, the order by clause makes the first row of every group the most recent like and distinct expression keeps only the first row for each person. Chris -- Chris Velevitch Manager - Adobe Platform Users Group, Sydney m: 0415 469 095 www.apugs.org.au Adobe Platform Users Group, Sydney October 2010: Flash Builder for SalesForce Date: 25th October, 6pm for 6:30 start Details and RSVP coming soon -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Group By Question
* Andrew E. Tegenkamp wrote: I have two tables and want to attach and return the most recent data from the second table. Table 1 has a counter ID and name. Table 2 has a counter ID, Reference (to Table 1 ID), Date, and Like. I want to do a query that gets each name and their most recent like. I have a unique key setup on likes for the reference and date so I know there is only 1 per day. I can do this query fine: SELECT test.people.id, test.people.name, test.likes.ref, MAX(test.likes.date) FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref GROUP BY test.people.id, test.people.name, test.likes.ref However, when I try to add in test.likes.id OR test.likes.likes I get an error that it has to be included in the Group By (do not want that) or has to be an aggregate function. I just want the value of those fields from whatever row it is getting the MAX(date) field. SELECT p.name, l.date, l.likes FROM people p LEFT JOIN (SELECT l1.ref, l1.date, l1.likes FROM likes l1 GROUP BY l1.ref, l1.date, l1.likes HAVING l1.date = (SELECT max(date) FROM likes WHERE ref = l1.ref)) l ON (p.id = l.ref); Or the newfangled way, replacing the inner subselect with a window: SELECT p.id, p.name, l.likes FROM people p LEFT JOIN (SELECT l1.ref, l1.likes, l1.date, max(l1.date) OVER (PARTITION BY ref) AS maxdate FROM likes l1) l ON (p.id = l.ref AND l.date = l.maxdate); On this "dataset", the windowed version is estimated to be ~ 60% faster than the grouped one, and the actual execution time is ~ 20% lower. -- Christian -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Group By Question
Andrew E. Tegenkamp wrote: I have two tables and want to attach and return the most recent data from the second table. Table 1 has a counter ID and name. Table 2 has a counter ID, Reference (to Table 1 ID), Date, and Like. I want to do a query that gets each name and their most recent like. I have a unique key setup on likes for the reference and date so I know there is only 1 per day. I can do this query fine: SELECT test.people.id, test.people.name, test.likes.ref, MAX(test.likes.date) FROM test.people LEFT JOIN test.likes ON test.people.id = test.likes.ref GROUP BY test.people.id, test.people.name, test.likes.ref However, when I try to add in test.likes.id OR test.likes.likes I get an error that it has to be included in the Group By (do not want that) or has to be an aggregate function. I just want the value of those fields from whatever row it is getting the MAX(date) field. How can I return those values? I believe you'll need to use SQL subqueries to force a different order of operations, such as group/max before join, and so on. Something like this: SELECT test.people.id, test.people.name, filt_likes.ref, filt_likes.date, filt_likes.likes FROM test.people LEFT JOIN ( SELECT test.likes.* FROM test.likes INNER JOIN ( SELECT ref, MAX(date) AS max_date FROM test.likes GROUP BY ref ) AS filt ON test.likes.ref = filt.ref AND test_likes.date = filt.max_date ) AS filt_likes ON test.people.id = filt_likes.ref Try testing that. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Group By question
Jeff Lanzarotta wrote: Sam Mason <[EMAIL PROTECTED]> wrote: On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote: Hello, I have a table that looks something like this: SKU Dept Col1 Col2 Col3 --- -- --- --- -- 1112 3 2123 4 3210 1 4201 2 5241 3 6312 3 I am having a problem trying to get the Is there a query that can do something like this: select sku, dept, (col1 + col2) * col3) from table group by dept What are you expecting the group by to do here? It may be helpful if you show what you expect the output to be. > Okay, actually the query is something like: > > select dept, (col1 + col2) * col3) from table group by dept > > So, the output would look something like: > > DeptTotal > -- --- > 1 26 > 2 18 > 3 9 > Please don't top-post. The problem may have been that you were selecting SKU (at least, in the first example). But, as you're aggregating the columns, this is impossible. SELECT Dept, SUM((Col1 + Col2) * col3) AS total FROM foo GROUP BY Dept ORDER BY Dept; dept | total --+--- 1 |29 2 |18 3 | 9 (your example had an arithmetic error) brian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Group By question
Okay, actually the query is something like: select dept, (col1 + col2) * col3) from table group by dept So, the output would look something like: DeptTotal -- --- 1 26 2 18 3 9 Sam Mason <[EMAIL PROTECTED]> wrote: On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote: > Hello, > > I have a table that looks something like this: > > SKU Dept Col1 Col2 Col3 > --- -- --- --- -- > 1112 3 > 2123 4 > 3210 1 > 4201 2 > 5241 3 > 6312 3 > > I am having a problem trying to get the Is there a query that can do > something like this: > > select sku, dept, (col1 + col2) * col3) from table group by dept What are you expecting the group by to do here? It may be helpful if you show what you expect the output to be. Sam ---(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: [GENERAL] Group By question
On 10/16/07, Jeff Lanzarotta <[EMAIL PROTECTED]> wrote: > Hello, > > I have a table that looks something like this: > > SKU Dept Col1 Col2 Col3 > --- -- --- --- -- > 1112 3 > 2123 4 > 3210 1 > 4201 2 > 5241 3 > 6312 3 > > I am having a problem trying to get the Is there a query that can do > something like this: > > select sku, dept, (col1 + col2) * col3) from table group by dept So, what would the output look like? For instance, you've got these two lines at the top: SKU Dept Col1 Col2 Col3 --- -- --- --- -- 1112 3 2123 4 If we group by dept, then how do I handle those two rows? Which SKU would be the right one? Would the answer be ((sum(col1)+sum(col2))*sum(col3)) ?? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Group By question
On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote: > Hello, > > I have a table that looks something like this: > > SKU Dept Col1 Col2 Col3 > --- -- --- --- -- > 1112 3 > 2123 4 > 3210 1 > 4201 2 > 5241 3 > 6312 3 > > I am having a problem trying to get the Is there a query that can do > something like this: > > select sku, dept, (col1 + col2) * col3) from table group by dept What are you expecting the group by to do here? It may be helpful if you show what you expect the output to be. Sam ---(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
