Hi I am bit confused.. If you want to display first 5 the following query will fetch top 5 book id's. I am not able to understand, why there is a sub-query.
SELECT ELEMENT_ID , COUNT( * ) FROM WATCH_LIST_ELEMENT GROUP BY ELEMENT_ID ORDER BY COUNT(*) DESC LIMIT 5 Regards, R.Muralidharan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Dan Langille Sent: Tuesday, April 26, 2005 7:52 AM To: pgsql-sql@postgresql.org Cc: [EMAIL PROTECTED] Subject: [SQL] people who buy A, also buy C, D, E The goal of my query is: given a book, what did other people who bought this book also buy? I plan the list the 5 most popular such books. In reality, this isn't about books, but that makes it easier to understand I think. We have a table of customer_id (watch_list_id) and book_id (element_id). freshports.org=# \d watch_list_element Table "public.watch_list_element" Column | Type | Modifiers ---------------+---------+----------- watch_list_id | integer | not null element_id | integer | not null Indexes: "watch_list_element_pkey" primary key, btree (watch_list_id, element_id) "watch_list_element_element_id" btree (element_id) Foreign-key constraints: "$2" FOREIGN KEY (watch_list_id) REFERENCES watch_list(id) ON UPDATE CASCADE ON DELETE CASCADE "$1" FOREIGN KEY (element_id) REFERENCES element(id) ON UPDATE CASCADE ON DELETE CASCADE freshports.org=# I have a query which returns the needed results: SELECT W.element_id FROM watch_list_element W WHERE w.watch_list_id in (select watch_list_id from watch_list_element where element_id = 54968) GROUP BY W.element_id ORDER BY count(W.watch_list_id) DESC LIMIT 5; But performance is an issue here. So I'm planning to calculate all the possible values and cache them. That is, given each element_id in a watch_list, what are the top 5 element_id values on all the lists on which the original element_id appears? I'm having trouble constructing the query. I'm not even sure I can do this in one select, but that would be nice. Examples and clues are appreciated. Any ideas? Thank you. -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ NEW brochure available at http://www.bsdcan.org/2005/advocacy/ ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
