order-by

SQL Server 2008: Ordering by datetime is too slow

My table (SQL Server 2008) has 1 million+ records, when I try to order records by datetime, it takes 1 second, but when I order by ID (int), it only takes about 0.1 second. Is there any way to improve the efficiency? (I already added the datetime column to the index) ...

How to get two random records with Django

How do I get two distinct random records using Django? I've seen questions about how to get one but I need to get two random records and they must differ. ...

How to sort model by values in a dictionary?

In Django, I have a model, and I will have a dictionary having the id's of objects in this model as keys, and a weight as values. I would like to use these weights in an order_by: MyModel.objects.filter(title__icontains=query).order_by( 'value_from_the_dictionary' ) How to make this work? I can't put the weights in the model, as they...

LINQ, Skip, OrderBy, and SQL Server 2000

I'm accessing a data context object that is auto-generated by using LINQ to SQL. The SQL database is a SQL Server 2000 box. The class I'm working with is a SQL View. I have a statement that is similar to this: query = _context.OrderDetails .Where(w => w.Product == "TEST") .OrderBy(o => o.DateCompleted) .ThenBy(t => t.LineIte...

MySQL Wrong ORDER BY

SELECT `player`.`cid`, `player`.`k`, `player`.`d`, `gg`.`gg_id`, `gg`.`name`, `gg`.`img`, `cc`.`cid`, `cc`.`name`, `cc`.`class`, `cc`.`gg_id` FROM `player` LEFT JOIN `cc` ON `cc`.`cid` = `player`.`cid` LEFT JOIN `gg` ON `gg`.`gg_id` = `cc`.`gg_id` ORDER BY (`k`-`d`) DESC i want to order by the K minus the D values, but im not gettin...

How does mySQL handle dynamic value within ORDER BY

It stumbled upon me while I was reading the query in another post. Take the following query for example (ignore the non-practical use of the ordering): SELECT * FROM Members ORDER BY (TIMESTAMPDIFF(FRAC_SECOND, DateCreated , SYSDATE())) Say "Members" table has a huge row count (or the query is complex enough for it to be executed o...

orderby in sql query

Hello, I need to order sql query by a column (the three different values in this column are C,E,T). I want the results in order of E,C,T. So, of course I can't use ascending or descending orderby on this column. Any suggestions how can I do this? I don't know if that matters or not but I am using sybase data server on tomcat. ...

AlphaNumeric ordering in SQL vs. LINQ and .NET

I encountered an interesting thing today that I have never noticed before. It appears that SQL and LINQ order AlphaNumeric strings differently. Data table contains rows: A G 6 P 1 D J 2 T Z 9 F 0 If I perform an Order By in the SQL, I receive the following results: A D F G J P T Z 0 1 2 6 9 Now consider this LINQ sample: class Progr...

SQL best practice to deal with default sort order

A lot of SQL code I've read, it seems like the developer assumes that the default sort order always hold. For example when building an HTML select list they would just SELECT id, name FROM table without issuing an ORDER BY clause. From my own experience it seems like dbms alway order data using FIFO if no ORDER BY clause is given and n...

SQL order by a column from another table

Hello, I have 3 tables: people, groups and memberships. Memberships is a join table between people and groups, and have 3 columns: personId, groupId and description (text). I want to select entries from the memberships table depending on a groupId but sorting the result by the names of people associated to the found memberships (name i...

How to reverse the default ordering in Mysql?

In Mysql, when you execute a select SQL statement, there is a default ordering if you don't include a sorting clause, how to reverse the default ordering? Just add DESC? ...

SQL Server: How to order by date, if the date is < GetDate()

Hi, Here's an interesting one... hope I can explain it well... I have a collection of competitions in a single table in my SQL Server DB. I'm doing a full text search over them, which works fine. However, some of the competitions are closed, and I want these closed comps to show up after the open comps, while still respecting the rank ...

MySQL Order by multiple column combined (not order by field1 asc, field2 asc)

Hi all, it seems like a typical question but it's different. I have a table with an id and 3 timestamp fields (to simply). Initially all 3 fields are null, and they get filled with values. Examples of rows are: id time1 time2 time3 1 1259625661 1259643563 null 2 null 1259621231 null 3 1259625889 null 1259644511...

Sort blank entries to bottom of LINQ query.

I am trying to sort a LINQ to SQL query based on two fields. The first field is occasionally null which automatically sorts to the top of an ascending query. Is there any way to make the null entries sort to the bottom? Here is an example: From x in SampleDataContext.Event _ Order By x.Date, x.Sequence_Number _ Select x.Date, x.Seq...

SQL ORDER by `no` with NULLs at the end

Hi! I have got a MySql query that orders me results by no column (int, can be null). Simple example: SELECT * FROM table ORDER BY no ASC I would like to get a resultset sorted like 1, 2, 3, 10, 52, 66, NULL, NULL, NULL but I get NULL, NULL, NULL, 1, 2, 3, 10, 52, 66 Is it possible with SQL query ? ...

How can I sort by the id of a ManyToManyField in Django?

I've got a ManyToManyField in a user object and it's used to map the users that user is following. I'm trying to show a subset list of who they have most recently followed. Is there a trick in .order_by() that will allow me to sort by the id of the ManyToManyField? The data is there, right? # (people the user is following) following = m...

Zend framework SQL select query construction (ORDER BY)

I have a database with VARCHAR column url. I would like to fetch rows so that the ones that have any url value have priority over other rows, but are ordered by date row (descending), so ORDER BY 'url' DESC, 'date' DESC wouldn't work as it would order them alphabetically first. Basically, it would look something like this: Table: ID ...

MySQL row order for "SELECT * FROM table_name;"

Assume that the following query is issued to a MySQL database: SELECT * FROM table_name; Note that no ORDER BY clause is given. My question is: Does MySQL give any guarantees to which order the result set rows will be given? More specifically, can I assume that the rows will be returned in insertion order (that is the same order in ...

How can I sort data before grouping in MYSQL

I have a big mysql query which needs to trawl through 4 tables to get all the 'items' for my application. Each item can have many categories and each user can have up to one of each item. The items and categories are easy: SELECT Items.itemId, Items.name, Items.type, Categories.name AS category FROM Items LEFT JOIN ItemCategories ON I...

Linq to sql - dynamic order by with IN operator

hello, i have the following linq query: using (var db = new MyDataContext()) { int[] onlineUsers = GetOnline(); var users = (from u in db.Users orderby (onlineUsers.Contains(u.u_username) && u.u_hasvisible_photo) descending, u.u_lastlogin descending select u.u_username).Skip(startRowIndex).Take(maximumRows).ToList(); } ...