Tag Info

New answers tagged

1

It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes). Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y) then the data is more likely to be in a ...


0

I've solved it by doing the following: SELECT nWidth, nHeight, nColumn, (SELECT MAX(t.nRow + (t.nHeigth - 1)) FROM test t WHERE t.nRow = tp.nRow GROUP BY t.nRow LIMIT 1) AS nRow FROM test tp ORDER BY nRow, tp.nColumn This will get the max from the row number plus it's heigth minus 1. So, if we have a row that has a cell with height equal or higher ...


3

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction. But in the case you ...


2

One way: SELECT id FROM products WHERE details->'make' = ? OR details->'model' = ? OR details->'year' = ? ORDER BY CASE WHEN details->'make' = ? THEN 0 ELSE 1 END + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END + CASE WHEN details->'year' = ? THEN 0 ELSE 1 END; It's a bit simpler to invert the logic: ...


0

Can you please post an example of your query? Without knowing what your query structure look likes, this will be difficult to fix. However here is an example that may point you in the right direction based off of your post title, in regards to ORDER and GROUP: SELECT pkey, COUNT(int), FROM my_table LEFT JOIN this_table USING (key) GROUP BY 1 ...


2

There is no "default" order in the RDBMS. Just because tables are not lists but sets. Imagine records as a cards in a big bag, not in a stack. There is no guarantee that cards will be fetched from the bag in some specific order. Yes, cards are enumerated by primary key, but you have to request the fetching in that specific order. It is not the default order ...


1

(this grew a little long to be a comment) As miracle173 says, this is not standard SQL as there are fields in the SELECT clause that are neither in the GROUP BY or in an aggregation function, meaning their values are undefined: for any given piece.Name there could be any number of prices and providers by there is only space to output up to one. This means ...


1

The order of execution for SQL query is FROM & JOIN -determine table & filter rows WHERE -more filters on the rows GROUP BY -Combine rows in group HAVING - filters on group ORDER BY - arrange the remaining rows Does group by get a random record from the table ? Answer is No. Is Order by working first and then Group by gets the first record? ...


4

Use PK columns Sorting by all columns of a table is potentially (very) expensive or sometimes simply impossible. Example: table with a json column or xml, or point or one of many other data types which cannot be used in ORDER BY, lacking the necessary operators. You get an exception. If it doesn't fail, it should work for your purpose, but it still would ...


2

without bothering to list every column in the ORDER BY clause For just that goal, ORDER BY tablename.* does work. If there are several tables involved, we can always write: SELECT * FROM (select ... complex query...) as T order by T.*; If the resultset has many wide rows making the sort inefficient, sorting a hashed representation instead might be a ...



Top 50 recent answers are included