New answers tagged

3

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 ...


-1

The answer is pretty simple. A UNION implies an order by to be able to find duplicate results. Your example: select 4,5,6 from dual union select 1,2,3 from dual union select 4,5,6 from dual Step by Step Put results of all queries together: 4,5,6 1,2,3 4,5,6 DISTINCT the result: 1st step of distinct: ORDER BY (all columns) 1,2,3 4,5,6 2ed step ...


5

This should work: SELECT id, name FROM data WHERE id <= 8 AND id >= ( SELECT MAX(d1.id) FROM data d1 LEFT JOIN data d2 ON d2.id = d1.id - 1 WHERE d2.id IS NULL ) ORDER BY id DESC ; The subquery look for the first gap (i.e. 5 to 6). See SQL Fiddle. Output: id | name 8 | Test 8 7 | Test 7 6 | Test 6


2

This might run faster due to "lazy evaluation". Note that you want to fetch some large columns, yet thousands of rows need to be looked at before deciding which 10 are desired. Instead of gathering all the columns needed, let's get just the PRIMARY KEYs, then reach back into posts only 10 times to get the bulky columns. Note that bulky columns are stored ...


-1

I hope I am not reading this wrong but if you want the top 10 most recent posts of a single user wouldn't it be better to run your query on the relations table and join the posts to that query? You would restrict your search to only the posts for the signle user you are attempting to look up then sort the data. So it would look like this: SELECT p.id, ...


2

With your datasets, MySQL has to obtain those 450,000 records from posts (in 1000 little chunks from each matching source_id), sort it, and then return the top 10. It is a costly exercise. You could resort to using a stored procedure, and accumulate results going back in time, say daily or weekly, looping until obtaining at least 10 records, and then ...



Top 50 recent answers are included