Hot answers tagged

16

For a single string You can apply the window function row_number() to remember the order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the original position in the string. You could try and simply omit the ORDER BY to get the position "as is": SELECT *, row_number() OVER () AS rn ...


15

BOL: A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. NULL means unknown. No other interpretation is valid. If that's ...


12

There is no magic solution to this type of problem. To avoid a potentially expensive sort, there has to be an index that can provide the requested order (and the optimizer must choose to use that index). Without a supporting index, the best SQL Server can do natively is to restrict the qualifying rows (based on the WHERE clause) before sorting the resulting ...


9

The LEFT JOIN in @dezso's answer should be good. An index, however, will hardly be useful (per se), because the query has to read the whole table anyway - the exception being index-only scans under PostgreSQL 9.2 and favorable conditions, see below. SELECT m.hash, m.string, count(m.method) AS method_ct FROM methods m LEFT JOIN nostring n USING (hash) ...


7

Let me try to explain why you should not do that, why you should never assume that an SQL-product will return a result set in a specific order, unless you specify so, whatever indices - clustered or non-clustered, B-trees or R-Trees or k-d-trees or fractal-trees or whatever other exotic indices a DBMS is using. Your original query tells to the DBMS to ...


6

Group by Abandoned_ID, WorkType_ID , Site_Type_ID, time_id One way to implement a group by is to sort the input. Stream Aggregate Showplan Operator: The Stream Aggregate operator requires input ordered by the columns within its groups. The optimizer will use a Sort operator prior to this operator if the data is not already sorted due to a prior Sort ...


6

select id, name, v[1] as major_version, v[2] as minor_version, v[3] as patch_level from ( select id, name, string_to_array(version, '.') as v from versions ) t order by v[1]::int desc, v[2]::int desc, v[3]::int desc; SQLFiddle: http://sqlfiddle.com/#!15/c9acb/1 If you expect more elements in the ...


6

I can reproduce the plan that you describe on SQL Server 2012 (on prem) by running the DDL in your question and then fiddling the stats so SQL Server thinks that the table is much larger than reality. UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000 And then running the query with OPTION (MAXDOP 1, CONCAT UNION, ORDER ...


5

You should append EXPLAIN EXTENDED before query and see result yourself. It should have an entry for possible_keys If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for ...


5

You are correct that NULL can mean 'Indeterminant' or 'Uknownn' or 'Not known yet' or 'Not applying'. But there is no reason to put the Nulls first or last. If we don't know the actual values, then tehy can be small or large. I think the standard for determinign the wanted behaviour of Nulls during sorting, is: ORDER BY test NULLS LAST ...


5

Some options: Persist the sorted version of your data to a table via trigger, and use it. Use Oracle Locale Builder to build a custom sort order. (Caveat: I have never used this, so I do not know what gotchas may exist there.) You could then use the NLSSORT function with that custom sort order.


5

For your request: sorted by alphabets and then by numeric values I assume (deriving from your sample data) you want to ORDER BY: The first letter, treated as text. The first number (consecutive digits), treated as integer. The whole string to break remaining ties, treated as text. May or may not be needed. SELECT * FROM tbl ORDER BY left(col, ...


5

Try this: CREATE TABLE SortTest (val char(1)) INSERT INTO SortTest VALUES ('A'), ('A'), ('A'), ('B'), ('B'), ('B') SELECT val FROM SortTest ORDER BY row_number() OVER (PARTITION BY val ORDER BY val), val


5

The amount of memory needed to perform a sort is not as simple as computing the raw size of the input data. The main sorting algorithm used by SQL Server is a variation on merge sort, which includes extra steps like key normalization to ensure all combinations of data column types can be sorted efficiently. Due to these extra steps, it is not easy to ...


5

You can use a CASE expression in the ORDER BY: ORDER BY CASE WHEN EndDate IS NULL 0 ELSE 1 END ASC , EndDate DESC , StartDate DESC Sort criteria in order (link): Values with a NULL EndDate will get a 0 and others will get a 1. This is used as the first sort criteria. Then the second criteria is EndDate. Finally ties will be sorted by StartDate. ...


4

Things are more complicated than that. Here are a few points of consideration. First, this entire discussion assumes B-Trees or B+ Trees (Hence the o(log(n))). There are other types of indexes, like hash indexes, where access is in O(1). Your question insinuates you're looking up values using "equals" search (e.g. looking for X=17). But in this particular ...


4

Welcome to DBA.SE! You can try to rephrase your query like this: SELECT m.hash, string, count(method) FROM methods m LEFT JOIN nostring n ON m.hash = n.hash WHERE n.hash IS NULL GROUP BY hash, string ORDER BY count(method) DESC; or another possibility: SELECT m.hash, string, count(method) FROM methods m WHERE NOT EXISTS (SELECT hash ...


4

Oracle says about Indexes and Index-Organized Tables under Full Index Scan: In a full index scan, the database reads the entire index in order. Yet, unter Fast Full Index Scan, it reads: A fast full index scan is a full index scan in which the database accesses the data in the index itself without accessing the table, and the database reads the index ...


4

Another approach is to add a function-based index on FN_SPECIAL_SORT_KEY(sorted_column,'asc'). Avoids the need for an extra column+trigger, and you won't need to modify your queries.


4

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so: ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate You can find a list of supported sort orders here.


4

From Postgres documentation: Chapter 11. Indexes (note that the same holds even for really old versions like 8.3 chapter 11. Indexes): By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on a column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS ...


4

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says: Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size ...


4

To produce your desired output, you can simply: SELECT id, version FROM versions ORDER BY string_to_array(version, '.')::int[]; One can cast a whole text array to an integer array (to sort 9 before 10). One can ORDER BY array types. This is the same as ordering by each of the elements. SQL Fiddle (reusing @a_horse's fiddle, thanks!)


4

MS Access is rather limited. I assume that it is possible to have more than one invoice for the same date. In this case I'll pick an invoice with the highest ID. At first we'll find maximum Invoice Date for each Food Item. SELECT FPD1.[Food item ID] AS ItemID ,MAX(I1.[Invoice Date]) AS MaxDate FROM [Food purchase data] AS FPD1 INNER JOIN ...


4

Unless your queries are ordered (with an explicit ORDER BY ) the data returned from queries is not guaranteed to be in any order. They just happen to come out in a certain order and that order is typically static unless something massively changes in the tables or in the RDBMS kernel. This is not unique to ORACLE. If the queries expect ordered data, I would ...


4

NLS_LANGUAGE determines the sort order. If you've went from, for example, FRENCH to the defaut, AMERICAN, you would get the difference in sort order you describe. To set the default NLS_LANGUAGE execute alter system set nls_language ='FRENCH' scope=spfile; and bounce the instance. You can always override this in the session using alter session set ...


4

There is no easy, built-in means of doing this, but here is a possibility: Normalize the strings by reformatting them into fixed-length segments: Create a sort column of type VARCHAR(50) COLLATE Latin1_General_100_BIN2. The max length of 50 might need to be adjusted based on the max number of segments and their potential maximum lengths. While the ...


3

Let's look at the WHERE clause WHERE finished IS NULL AND locked='0' AND created <= NOW() AND counter <= 5 AND company_id = 2 ORDER BY business_object_priority DESC, created ASC, id ASC Since company_id and locked are static values, they should be the leading columns of the index. Your ORDER BY has ...


3

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not. However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character ...


3

I don't know why it's done that way, but by definition NULLS can't be compared to non-NULLS, so they either have to go at the start or the end (Mark's answer covers this in a lot more detail). To get the behaviour you want - As far as I know there's no sorting option to put nulls last, so you have to bodge it by using a computed column to force them last. ...



Only top voted, non community-wiki answers of a minimum length are eligible