Hello, such a problem, you need to exclude data with the same idclient, to return any string no matter what the main one. DISTINCT as I understood it will not help, I tried GROUP BY, but it gives an error: -1; ORA-00979: the expression is not a GROUP BY expression. The query itself:

SELECT idclient, id, name, iditemtype, price FROM items WHERE LOWER(name) LIKE '%' || LOWER(xdb_access.cnv(in_find) || '%') AND isVisible = 1 AND xdb_access.equalsDateMinute(sysdate, isonline) <= t_time_online AND idclient != in_idclient GROUP BY idclient
  • one
    ru.stackoverflow.com/a/572040/194569 or use group functions for all columns except idclient. If there is no difference what kind of data is returned, it may be easier to take such maxima (although the values ​​of the columns may be from different records, but maybe this will not be critical for you) - Mike

2 answers 2

If you want to use analytics, then remove the group by:

 select * from ( SELECT idclient, id, name, iditemtype, price, row_number() over (partition by idclient order by idclient, id, name, iditemtype, price) rn FROM items WHERE LOWER(name) LIKE '%' || LOWER(xdb_access.cnv(in_find) || '%') AND isVisible = 1 AND xdb_access.equalsDateMinute(sysdate, isonline) <= t_time_online AND idclient != in_idclient ) where rn = 1
  • A little hurried with the correct choice, the fact is that if there is only one idclient, then the query returns nothing. How can this be fixed? - Son of God
  • Although no, everything works correctly, I figured out the reason, but I can’t understand why it came about at all - the Son of God

Try numbered strings analytics and choose the first (or any other)

 select * from ( SELECT idclient, id, name, iditemtype, price, row_number() over (partition by idclient order by idclient) rn FROM items WHERE LOWER(name) LIKE '%' || LOWER(xdb_access.cnv(in_find) || '%') AND isVisible = 1 AND xdb_access.equalsDateMinute(sysdate, isonline) <= t_time_online AND idclient != in_idclient GROUP BY idclient ) where rn = 1
  • Unfortunately, it will not work. The same mistake as in the question. - 0xdb