Cannot avoid duplicate results

[musaka87]

I have 3 tables (Items, ItemProperties and ItemPropertyKeys). ItemPropertyKeys contains of property keys like title, quantity, author for the items and ItemProperties contains the actual property (the name of the author, the title of the item etc.) There could be more than one property refering to one key for a single item (for example the item could have more than one names).

Example:
Item SomeSong
ItemPropertyKey title
ItemProperty #1 with key title -> "Lalala"
ItemProperty #2 with key title -> "Bla bla bla"

Wanted Result:
Item Key Title
SomeSong title "Lalala"(or "Bla bla bla")

Received Result so far
Item Key Title
SomeSong title "Lalala"
SomeSong title "Bla bla bla"

I need to select items (for example by date range) and theirs titles but if an item has more than one title I need to select only one of them.
I've tried everything so far and had no luck. And also I shouldn't use stored procedures.

Hope I've been clear enough.

Help will be appreciated.

Thanks in advance.

[amitpatel66]

Could you please post your source code that you have tried do far?

[musaka87]

 
SELECT ITEMID AS "ItemId",DOWNLOADEDDATE AS "Date",ITEMTYPE AS "Type",ITEMPROPERTY AS "Title", SUM(DOWNLOADS) AS "Downloads" FROM ( SELECT ITEMID,DOWNLOADEDDATE, ITEMTYPE, ITEMPROPERTY, DECODE (((DOWNLOADEDDATE-TO_DATE('2008-03-02')-ABS(DOWNLOADEDDATE-TO_DATE('2008-03-02'))+TO_DATE('2008-04-03')-DOWNLOADEDDATE-ABS(TO_DATE('2008-04-03')-DOWNLOADEDDATE))), 0, 1, 0) DOWNLOADS FROM (SELECT I.ITEMID,IT.ITEMTYPE,ITP.ITEMPROPERTY,DOWNLOADEDDATE FROM ITEMDOWNLOADS IDO JOIN ITEMS I ON I.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTIES ITP ON ITP.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTYKEYS ITPK ON ITPK.ITEMPROPERTYKEYID=ITP.ITEMPROPERTYKEYID AND (ITPK.ITEMPROPERTYKEY='Title') JOIN PROVIDERS P ON P.PROVIDERID=I.PROVIDERID AND P.PROVIDER='TestProvider' JOIN ITEMTYPES IT ON IT.ITEMTYPEID=I.ITEMTYPEID WHERE IDO.DOWNLOADEDDATE>=:start AND IDO.DOWNLOADEDDATE<=:end ))GROUP BY ITEMID,DOWNLOADEDDATE,ITEMTYPE,ITEMPROPERTY ORDER BY DOWNLOADEDDATE
 

This is one of my not working scripts and here's the result:
ID Date Type Title Value
1031 2008-03-27 Melodies Hladna Nejnost 1
1031 2008-03-27 Melodies Хладна нежност 1
1040 2008-03-31 Application GGG 1
1060 2008-04-01 Melodies Hladna Nejnost 1
1060 2008-04-01 Melodies Хладна нежност 1


The items with 1031 and 1040 ids are duplicated because they have 2 titles. I need to avoid this and result should contain only one of the titles no matter which one.

[amitpatel66]

Try this:

SELECT ITEMID AS "ItemId",DOWNLOADEDDATE AS "Date",ITEMTYPE AS "Type",MAX(ITEMPROPERTY) AS "Title", SUM(DOWNLOADS) AS "Downloads" FROM ( SELECT ITEMID,DOWNLOADEDDATE, ITEMTYPE, ITEMPROPERTY, DECODE (((DOWNLOADEDDATE-TO_DATE('2008-03-02')-ABS(DOWNLOADEDDATE-TO_DATE('2008-03-02'))+TO_DATE('2008-04-03')-DOWNLOADEDDATE-ABS(TO_DATE('2008-04-03')-DOWNLOADEDDATE))), 0, 1, 0) DOWNLOADS FROM (SELECT I.ITEMID,IT.ITEMTYPE,ITP.ITEMPROPERTY,DOWNLOADEDDA  TE FROM ITEMDOWNLOADS IDO JOIN ITEMS I ON I.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTIES ITP ON ITP.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTYKEYS ITPK ON ITPK.ITEMPROPERTYKEYID=ITP.ITEMPROPERTYKEYID AND (ITPK.ITEMPROPERTYKEY='Title') JOIN PROVIDERS P ON P.PROVIDERID=I.PROVIDERID AND P.PROVIDER='TestProvider' JOIN ITEMTYPES IT ON IT.ITEMTYPEID=I.ITEMTYPEID WHERE IDO.DOWNLOADEDDATE>=:START AND IDO.DOWNLOADEDDATE<=:END ))GROUP BY ITEMID,DOWNLOADEDDATE,ITEMTYPE ORDER BY DOWNLOADEDDATE
 

[musaka87]

Still doesn't work
I've tried it with min too but the result was the same

[amitpatel66]

 
SELECT ITEMID AS "ItemId",TO_DATE(DOWNLOADEDDATE) AS "Date",ITEMTYPE AS "Type",MAX(ITEMPROPERTY) AS "Title", SUM(DOWNLOADS) AS "Downloads" FROM ( SELECT ITEMID,DOWNLOADEDDATE, ITEMTYPE, ITEMPROPERTY, DECODE (((DOWNLOADEDDATE-TO_DATE('2008-03-02')-ABS(DOWNLOADEDDATE-TO_DATE('2008-03-02'))+TO_DATE('2008-04-03')-DOWNLOADEDDATE-ABS(TO_DATE('2008-04-03')-DOWNLOADEDDATE))), 0, 1, 0) DOWNLOADS FROM (SELECT I.ITEMID,IT.ITEMTYPE,ITP.ITEMPROPERTY,DOWNLOADEDDA    TE FROM ITEMDOWNLOADS IDO JOIN ITEMS I ON I.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTIES ITP ON ITP.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTYKEYS ITPK ON ITPK.ITEMPROPERTYKEYID=ITP.ITEMPROPERTYKEYID AND (ITPK.ITEMPROPERTYKEY='Title') JOIN PROVIDERS P ON P.PROVIDERID=I.PROVIDERID AND P.PROVIDER='TestProvider' JOIN ITEMTYPES IT ON IT.ITEMTYPEID=I.ITEMTYPEID WHERE IDO.DOWNLOADEDDATE>=:START AND IDO.DOWNLOADEDDATE<=:END ))GROUP BY ITEMID,TO_DATE(DOWNLOADEDDATE),ITEMTYPE ORDER BY DOWNLOADEDDATE
 

[musaka87]

I'm getting this:

Error starting at line 1 in command:

 
SELECT ITEMID AS "ItemId",TO_DATE(DOWNLOADEDDATE) AS "Date",ITEMTYPE AS "Type",MAX(ITEMPROPERTY) AS "Title", SUM(DOWNLOADS) AS "Downloads" FROM 
( SELECT ITEMID,DOWNLOADEDDATE, ITEMTYPE, ITEMPROPERTY, DECODE (((DOWNLOADEDDATE-TO_DATE('2008-03-02')-ABS(DOWNLOADEDDATE-TO_DATE('2008-03-02'))+TO_DATE('2008-04-03')-DOWNLOADEDDATE-ABS(TO_DATE('2008-04-03')-DOWNLOADEDDATE))), 0, 1, 0) DOWNLOADS FROM 
(SELECT I.ITEMID,IT.ITEMTYPE,ITP.ITEMPROPERTY,DOWNLOADEDDATE FROM 
ITEMDOWNLOADS IDO 
JOIN ITEMS I ON I.ITEMID=IDO.ITEMID  
JOIN ITEMPROPERTIES ITP ON ITP.ITEMID=IDO.ITEMID  
JOIN ITEMPROPERTYKEYS ITPK ON ITPK.ITEMPROPERTYKEYID=ITP.ITEMPROPERTYKEYID AND (ITPK.ITEMPROPERTYKEY='Title') 
JOIN PROVIDERS P ON P.PROVIDERID=I.PROVIDERID AND P.PROVIDER='TestProvider' 
JOIN ITEMTYPES IT ON IT.ITEMTYPEID=I.ITEMTYPEID 
WHERE IDO.DOWNLOADEDDATE>='2008-03-02' AND IDO.DOWNLOADEDDATE<='2008-04-03' ))
GROUP BY ITEMID,TO_DATE(DOWNLOADEDDATE),ITEMTYPE,ITEMPROPERTY ORDER BY DOWNLOADEDDATE
Error at Command Line:11 Column:71
Error report:
SQL Error: ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:    
*Action:
 

[musaka87]

Just removed TO_DATE from the GROUP BY clause but it still returns duplicate results.

[amitpatel66]

Just removed TO_DATE from the GROUP BY clause but it still returns duplicate results.

How is your date stored in the database? with the time stamp or only date?

[musaka87]

In the database it's just date.

[amitpatel66]

Try This:

SELECT ITEMID AS "ItemId",DOWNLOADEDDATE AS "Date",ITEMTYPE AS "Type",ITEMPROPERTY AS "Title", SUM(DOWNLOADS) AS "Downloads" FROM ( SELECT ITEMID,DOWNLOADEDDATE, ITEMTYPE, ITEMPROPERTY, DECODE (((DOWNLOADEDDATE-TO_DATE('2008-03-02')-ABS(DOWNLOADEDDATE-TO_DATE('2008-03-02'))+TO_DATE('2008-04-03')-DOWNLOADEDDATE-ABS(TO_DATE('2008-04-03')-DOWNLOADEDDATE))), 0, 1, 0) DOWNLOADS FROM (SELECT I.ITEMID,IT.ITEMTYPE,MAX(ITP.ITEMPROPERTY),DOWNLOADEDDA  TE FROM ITEMDOWNLOADS IDO JOIN ITEMS I ON I.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTIES ITP ON ITP.ITEMID=IDO.ITEMID  JOIN ITEMPROPERTYKEYS ITPK ON ITPK.ITEMPROPERTYKEYID=ITP.ITEMPROPERTYKEYID AND (ITPK.ITEMPROPERTYKEY='Title') JOIN PROVIDERS P ON P.PROVIDERID=I.PROVIDERID AND P.PROVIDER='TestProvider' JOIN ITEMTYPES IT ON IT.ITEMTYPEID=I.ITEMTYPEID WHERE IDO.DOWNLOADEDDATE>=:START AND IDO.DOWNLOADEDDATE<=:END GROUP BY  ITEMID,DOWNLOADEDDATE, ITEMTYPE))GROUP BY ITEMID,DOWNLOADEDDATE,ITEMTYPE,ITEMPROPERTY ORDER BY DOWNLOADEDDATE
 

Try this query. I am guessing because I cannot test in my machine without table structures and data.

[musaka87]

Still no change
Thanks for your time though

[amitpatel66]

I would suggest you to take out the property and select only other tables in your query. then add the table for item property and join your inline view and this table externally.....Just select one row of item property for each id.

[musaka87]

Thanks for the help.