order-by

mysql ORDER BY datetime type field not sorting as expected

I have a field in my database that stores the datetime that an item was added to the database. If I want to sort the items in reverse chronological order I would expect that doing ORDER by date_added DESC would do the trick. But this seems not to work. I also tried ORDER by UNIX_TIMESTAMP(date_added) but this still did not sort the resul...

MYSQL Select statment Order By with Group By

I have the following simple SQL statment SELECT id, name, value_name, value_id FROM table GROUP BY id ORDER BY value_id DESC when grouping I would like to get the value_name and value_id of the tuple where the value_id is the biggest. The way it is i am getting the smallest value. For example 1, name1, valuename, 3 (where i know ...

Unexpected behaviour of Order by clause

I have a table which looks like Col1 col2 col3 col4 col5 1 5 1 4 6 1 4 0 3 7 0 1 5 6 3 1 8 2 1 5 4 3 2 1 4 The script is declare @t table(col1 int, col2 int, col3 int,col4 int,col5 int) insert into @t select 1,5,1,4,6 union all select 1,4,0,3,7 union a...

problem with SQL CE and orderby linq clause

Hi, I am using linq with SQL CE, but for a simple query like this: var points=from i in this.DomainBoundaryPoints orderby i.Index select i; I get this error: Could not find an implementation of the query pattern for source type 'System.Data.Linq.EntitySet<DAL.DomainBoundaryPoint>'. 'OrderBy' not found. Are you missing a reference ...

SQL - Order against two columns at the same time (intersecting)

I have a table with the fields CommonName and FirstName. Only either field has data, never both. Is there a way to order rows in an intersecting manner on SQL Server? Example: CommonName FirstName Bern Wade Ashley Boris Ayana I want records ordered like this: CommonName FirstName Ashley Ayana Bern Bo...

Linq-to-sql orderby thenby

I'm using the following query syntax from table where where orderby orderby Where the first orderby is a date and second orderby is a date. I would assume this would work like orderby thenby but appears to be doing something else. 1 How can I do an orderby thenby using the above syntax without using extension syntax. (Got it) 2 An...

MySQL ORDER BY, use column mod_time, but if mod_time is 0, use column create_time

Hi I want to order results based on two columns, mod_time and create_time. I want the most recent to be present. At the moment I have ORDER BY pr.mod_time DESC, pr.create_time DESC This breaks if the item has not modified, in which case mod_time is 0 and only the create_time is set. This effectively puts anything with a mod_time of ...

How can I add a complex order-by using Hibernate Criteria?

I'm using Hibernate criteria and would like to add an order-by clause that is functionally the same as this SQL: order by abs(dateSubmitted - 125234234) Where dateSubmitted is a long and the number subtracted from it will be user-supplied (as a date). I'm trying to order records by their 'distance' from a user supplied date. I've tri...

Linq to NHibernate, Order by Rand() ?

Hi everybody, I'm using Linq To Nhibernate, and with a HQL statement I can do something like this: string hql = "from Entity e order by rand()"; Andi t will be ordered so random, and I'd link to know How can I do the same statement with Linq to Nhibernate ? I try this: var result = from e in Session.Linq<Entity> orderb...

Why does ROW_NUMBER OVER (ORDER BY column) return a different result order than just ORDER BY column?

I'm on SQL Server 2008, using NHibernate as persistence layer (although this problem is purely SQL, I believe). I've boiled down my problem to the following SQL statement: SELECT TOP 2 this_.Id as Id36_0_, this_.Name as Name36_0_, ROW_NUMBER() OVER (ORDER BY this_.IsActive) as MyOrder FROM Campsites this_ ORDER BY this...

MySQL specifying exact order with WHERE `id` IN (...)

Is there an easy way to order MySQL results respectively by WHERE id IN (...) clause? Example: SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3) to return Article with id = 4 Article with id = 2 Article with id = 5 Article with id = 9 Article with id = 3 and also SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3...

Alphanumeric Order By in Mysql

Hi, How can i make only numeric order by when the column containing alphanumeric characters in mysql ? column (name) is unique field. my table contains the records, id name 1 ab001 2 ab010 3 aa002 4 ac004 5 ba015 6 ba006 7 aa005 8 ac003 The results must be like this, id name 1 ab001 3 aa002 8 ac003 4 ac0...

Best way to fetch last 4 rows from a result set using mysql

Can any one please let me know, that, i need to fetch last 4 rows from a result-set using mysql. The result-set returns totally 6 records. but, i need the records to be fetch from last4...i.e, Record-3 Record-4 Record-5 Record-6 ...

C#/LINQ: How to define generically a keySelector for a templated class before calling OrderBy

Hi Folks, I have the following class defined in C# class myClass<T,U> { public T PropertyOne { get; set; } public U PropertyTwo { get; set; } } I need to write a function that reorder a list of myClass objects and takes two other parameters which define how I do this reorder: does my reordering depend on PropertyOne or Proper...

How to dynamically order many-to-many relationship with JPA or HQL?

I have a mapping like this: @ManyToMany(cascade = CascadeType.PERSIST) @JoinTable( name="product_product_catalog", joinColumns={@JoinColumn(name="product_catalog", referencedColumnName="product_catalog")}, inverseJoinColumns={@JoinColumn(name="product", referencedColumnName="product")}) public...

Custom sort logic in OrderBy using LINQ

What would be the right way to sort a list of strings where I want items starting with an underscore '_', to be at the bottom of the list, otherwise everything is alphabetical. Right now I'm doing something like this, autoList.OrderBy(a => a.StartsWith("_") ? "ZZZZZZ"+a : a ) ...

SQL Server 2005: Update rows in a specified order (like ORDER BY)?

I want to update rows of a table in a specific order, like one would expect if including an ORDER BY clause, but SQL Server does not support the ORDER BY clause in UPDATE queries. I have checked out this question which supplied a nice solution, but my query is a bit more complicated than the one specified there. UPDATE TableA AS Parent...

Complex orderby question (entity framework)

Ok, so I will start by saying that I am new to all this stuff, and doing my best to work on this project. I have an employee object, that contains a supervisor field. When someone enters a search on my page, a datagrid displays employees whose name match the search. But, I need it to display all employees that report to them and a third ...

Mysql slow query: INNER JOIN + ORDER BY causes filesort

Hello! I'm trying to optimize this query: SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` ON `posts`.id = `posts_tags`.post_id WHERE (((`posts_tags`.tag_id = 1))) ORDER BY posts.created_at DESC; The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different ...

How to order a HasMany collection by a child property with Fluent NHibernate mapping

I am using Fluent NHibernate to map the following classes: public abstract class DomainObject { public virtual int Id { get; protected internal set; } } public class Attribute { public virtual string Name { get; set; } } public class AttributeRule { public virtual Attribute Attribute { get; set; } public virtual Statio...