On Sat, Aug 07, 2010 at 12:40:41PM -0700, aravind chandu wrote: > Hello every one, > > I have encountered a problem while working .I have a sample table with the > following data > > > TID Date Item > T100 8/1/2010 Laptop > T100 8/1/2010 Desktop > T101 8/1/2010 Laptop > T102 8/1/2010 Desktop > T103 8/2/2010 Laptop > T103 8/2/2010 Desktop > T104 8/2/2010 Laptop > > need the data when a person bought laptop & desktop on the sameday. This is actually relatively straight-forward using modern PostgreSQL. Rather than counting, use direct aggregation to compare, so: SELECT TID, "Date" FROM "table" GROUP BY TID, "Date" HAVING ARRAY['Laptop','Desktop'] <@ array_agg(item); That last line checks whether the array created by array_agg contains at least the elements Laptop and Desktop. If you need an "equals" comparison rather than the above "contains or equals", you can sort both arrays canonically using the array_sort function below and then compare them with "=". CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY(SELECT * FROM unnest($1) ORDER BY 1); $$; The "=" query would look like this: SELECT TID, "Date" FROM "table" GROUP BY TID, "Date" HAVING array_sort(ARRAY['Laptop','Desktop']) = array_sort(array_agg(item)); Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
