Tag Info

Hot answers tagged

10

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


8

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


7

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


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


5

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


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.


4

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


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

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

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

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


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

Based on your description it seems like TRANSLATE can do the work for you. As Jeffrey Kemp suggests, a function based index could be created for this. Setup: drop table t1; create table t1 as ( select 'AB$$' c1 from dual union all select 'AB1$' from dual union all select 'ABz$' from dual union all select 'BZ' from dual union all select ...


3

For your request: sorted by alphabets and then by numeric values I am assuming (deriving from your sample data), that only the first letter should be treated as text to sort by (easy to adapt). Further assuming that you want to sort by the first number in the string next (digits only). To break ties with trailing characters I finally order by the ...


3

This is a way to broad question to give a detailed answer. However, there is one general rule that holds true in development: Don't reinvent the wheel That is, unless you have a good reason. In the case of sorting, the developers of the database engine spend a lot more time writing an efficient sorting algorithm than you will be able to. So unless you ...


3

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!)


2

A query in MongoDB can only use one index at a time, so it's a case of one or the other - it can't use the 2d index first, then do a sort on the _id index. In order to use indexes for both the selection and the sort, you would need a compound index like this: db.markers.ensureIndex( { latlng : "2d" , _id : 1 } ); Try that, or similar and see how it ...


2

with w as ( select 'AB1$' as foo from dual union all select 'aCC#' from dual union all select 'ac' from dual union all select 'BZ' from dual union all select '1' from dual union all select 'a' from dual union all select '!' from dual ) select foo from w order by regexp_replace(lower(foo), '[^a-z]', '~'), regexp_replace(foo, '[^0-9]', '~'), foo; ...


2

The elementary difference is that window functions are applied to all rows in a result set to compute additional columns after the rest of the result set has been determined. No row is dropped. They are available since PostgreSQL 8.4. The LIMIT and OFFSET clauses of the SELECT command on the other hand do not compute additional columns. They just pick a ...


2

You can tweak your query slightly to get a count of 'SpillToTempDb' i.e. Sort warnings, which would show you if any of the queries you're looking at could be an issue. WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT p.name, p.object_id, p.create_date, p.modify_date, s.last_elapsed_time, ...


2

If there is a foreign key from dw_fact_program_attendance(criteria_id) that REFERENCES dw_dimension_criteria(id), there is no need to join the two tables (but the optimizer is not yet smart enough to understand that.) You can have only one table and GROUP BY criteria_id: SELECT a.criteria_id AS c0, COUNT(DISTINCT a.client_id) AS m0 FROM ...


2

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


2

Your sortpath isn't technically being "changed" by the ORDER BY. The correct values are all there, just not coming up in the order you anticipated. In SQL, if you don't explicitly sort a set of data, the order in which it comes back to you is undefined... and the fact that it originally appeared to be in the anticipated order was more along the lines of ...


2

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


2

Well, first of all a SELECT * will pretty much void using any indexes. DB2 will see that you require the entire table and thus do a table scan (which in itself can be expensive). After that you are doing a an ORDER BY, which causes DB2 to do a SORT on the table it just scanned (another expensive operation). So you have two expensive operations one after the ...


2

create extension semver; select id, version from SoftwareReleases order by version::semver; http://www.pgxn.org/dist/semver/doc/semver.html


2

I have some rather distressing news: ORDER BY can still wreak some havoc with filesorts. With all the hype about this being addressed and fixed, there is simply no way to get InnoDB to effectively use the index on an ORDER BY. Start with the Ground Zero of InnoDB row data, the Clustered Index. Rows are tagged with a 6-byte transaction ID field a 7-byte ...



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