New answers tagged

4

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause. For the problem described in your question, you actually do not need a UNION at all. Use only the LIKE condition to cover both full and partial matches: WHERE name LIKE '%roma%' Then use a ...


4

The simple answer is.... To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause. The Oracle documentation spells it out clearly: Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve ...


1

If I'm reading this correctly you want to see all the full matches followed by all the partial matches with sorting within each set. If that is the case then you can add a column to your column list as below; SELECT * FROM (SELECT 'FULL' as match_type, column_1, column_2 FROM a_table UNION SELECT 'PART' as match_type, column_1, column_2 FROM ...


3

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs I took the code from the GetFamilyTree function and wrote it as a procedure for you STORED PROCEDURE DELIMITER $$ DROP PROCEDURE IF ...


1

The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;



Top 50 recent answers are included