Hot answers tagged sorting
6
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 ...
5
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)
...
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
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
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
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
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 ...
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
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
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.
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
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
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
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 ...
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
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
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 ...
1
As @ypercube pointed out in the comments, my query is quite close to the correct answer. The sort by avg_pval DESC, avg_margstat is actually close to the correct sort, only incorrect if the (avg_pval, margstat) tuple happens to have ties. So one can sort again, for a fixed (avg_pval, margstat), on datafile_id, index which will bring the groups back together. ...
1
Since hash is an md5, you may probably try to convert it in a number: you may store it as a number, or just create a functional index that calculate that number in a immutable function.
Other people already created a pl/pgsql function that convert (part of) an md5 value from text to string. See ...
1
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 ...
1
Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.
Very roughly
SQL
SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);
Code
Function SortOrder(ToSort)
Part1 = Left(ToSort, 1)
For i = 2 To Len(ToSort)
If Not IsNumeric(Mid(ToSort, i, 1)) Then
...
1
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 ...
1
You need an index on (c2, c1) for this query, not an index on (c1, c2).
Of course it would be best if there was an index (c2 DESC, c1 DESC) for the first and an index (c2 DESC, c1 ASC) for the second query. Unfortunately MySQL cannot create such indexes. The ASC and DESC are ignored.
Which means your two indexes:
CREATE INDEX index_1 ON t1 (c1 DESC, c2 ...
1
Think of the index like the business section of the phone book.
There are multiple levels of sorts:
a would be category, like "Doctors"
b would be last name, like "Smith"
c would be first name, like "John"
The listings (rows) are sorted in order of Category -> Last Name -> First name
Using this model, how can you find doctors named John without knowing ...
1
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;
...
Only top voted, non community-wiki answers of a minimum length are eligible
