order-by

LinqtoSQL filter and order by syntax

My Techie Bretheren (and Sisteren, of course!), I have a LinqToSql data model that has the following entities: I need to retrieve all advisors for a specific office, ordered by their sequence within the office. I've got the first part working with a join: public static List<Advisor>GetOfficeEmployees(int OfficeID) { List<Advisor>...

ordering by collection count in nhibernate

Is there a way of ordering a list of objects by a count of a property which is a collection? For arguments sake let's say I have a question object with a question name property, a property that is a collection of answer objects and another property that is a collection of user objects. The users join the question table via foreign key ...

Case statement in SQL Rownumber() Order By clause not working on varchar and int values

Why doesn't this work? DECLARE @temp table (ShipNo int, Supplier varchar(10) ) INSERT INTO @temp VALUES (1,'CFA') INSERT INTO @temp VALUES (1, 'TFA') INSERT INTO @temp VALUES (2, 'LRA') INSERT INTO @temp VALUES (2, 'LRB') INSERT INTO @temp VALUES (3, 'ABC') INSERT INTO @temp VALUES (4, 'TFA') Declare @OrderBy varchar(255) sET @OrderB...

The application sorts strings differently than the database

I am trying to sort a list of products by their name in a .Net application written in C#, to obtain the same list that I would get from an SQL Server database through an order by: select * from Products order by ProductName Unfortunately, the application sorting behaves differently than the database sorting. It is probably related to th...

linq: multiple order by

i have table movies and categories, i what get ordered list by categoryID first and then by Name movie table has columns: ID, Name, CategoryID category table has: ID, Name tried something like this but doesnt work: var movies=_db.Movies.OrderBy(m=>{ m.CategoryID, m.Name }) ...

Database consistency with SQL Order By and nulls

I have a column in my database (a flag) with type varchar(1) that is populated either Y or NULL (this is how it is, not in my control). In SQL Server, doing an ascending order by query, NULL is ordered at the top. Should this behaviour be consistent for Oracle and DB2? If, instead I have a COALESCE on the column to ensure it is not nu...

Linq to SQL: How can I Order By a composite object?

I have the following Linq to SQL query, in which I'm trying to do a multi-column GROUP BY: return from revision in dataContext.Revisions where revision.BranchID == sourceBranch.BranchID-1 && !revision.HasBeenMerged group revision by new Task(revision.TaskSourceCode.ToUpper(), ...

How to Order a SQL Query with grouped rows

I have the table (Product_Id, category priority, atribute1, atribute2...) in MS Access, and I am trying to make a query that orders the data grouped by Category and ordered by the highest priority. Priority can be Null, so it should be placed at the end. Example: Table 1, 100, 2, atr1, atr2 2, 300, , atr1, atr2 3, 100, 5, atr1, atr2 4,...

Ordering by the order of values in a SQL IN() clause

I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause. The problem is that I have 2 queries, one that gets all of the IDs and the second that retrieves all the information. The first creates the order of the IDs which I want the second to order by. The IDs are put in an IN() clause...

SQL Server UNION - What is the default ORDER BY Behaviour

If I have a few UNION Statements as a contrived example: SELECT * FROM xxx WHERE z = 1 UNION SELECT * FROM xxx WHERE z = 2 UNION SELECT * FROM xxx WHERE z = 3 What is the default order by behaviour. The test data I'm seeing essentially does not return the data in the order that is specified above. I.e. the data is ordered, but I wan...

Fetch records in the same order WITHOUT using ORDER BY clause

Hi, I am new to t-sql. Can someone tell me how to fetch records from a sql table in the same order WITHOUT using the order by clause? ...

SQL order by and left outer join doesn't have correct order

I have a view that is joining two tables and ordering by the first table. Except that the order isn't correct. It misses an occasional record, and then at the end, most of those records exist in order, and then at that end, the rest of the records exist in order. So it has records such as 1 (most of the records in order) 2 4 5 6 7 ...

What options do I have to make my ORDER BY faster?

I have the following query: SELECT DISTINCT c.id FROM clients AS c LEFT JOIN client_project AS cp ON (cp.client_id = c.id) WHERE cp.project_id = 1 AND c.active_flag = 1 ORDER BY c.client_name If I remove the order by, the query takes 0.005 seconds. With the order by, the query takes 1.8-1.9 seconds. I have an index on client_name....

SQL ORDER chars numerically

I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following: 100 131 200 21 30 31000 etc. How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this? Thank You. ...

PHP MySQL Order by Two Columns

I'm trying to sort some data by two columns, but it doesn't seem to be working. What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then the article title, then the article date) 50 | This article rocks | Feb 4, 2009 35 | This a...

MSSQL Paging is returning random rows when not supposed too

I'm trying to do some basic paging in MSSQL. The problem I'm having is that I'm sorting the paging on a row that (potentially) has similar values, and the ORDER BY clause is returning "random" results, which doesn't work well. So for example. If I have three rows, and I'm sorting them by a "rating", and all of the ratings are = '5' - t...

SQL in ASP.NET - Order By sorting only one type incorrectly

I'm working on an ASP.NET application which is supposed to output detector data for a chosen interstate, sorted by mile marker. We determined the best way to do this would be sorting it by longitude (West and East) or latitude(North and South) depending on which direction it goes. Here's the query that populates it. SELECT [ShortWebID],...

Special order by on SQL query

I need to display a list of records from a database table ordered by some numeric column. The table looks like this: CREATE TABLE items ( position int NOT NULL, name varchar(100) NOT NULL, ); INSERT INTO items (position, name) VALUE (1, 'first'), (5, 'second'), (8, 'third'), (9, 'fourth'), (15, 'fifth'), (20, 'sixth'); Now, the o...

MySQL - Control which row is returned by a group by.

I have a database table like this: id version_id field1 field2 1 1 texta text1 1 2 textb text2 2 1 textc text3 2 2 textd text4 2 3 texte text5 If you didn't work it out, it contains a number of versions of a row, and then ...

Optimizing "ORDER BY" when the result set is very large and it can't be ordered by an index

How can I make an ORDER BY clause with a small LIMIT (ie 20 rows at a time) return quickly, when I can't use an index to satisfy the ordering of rows? Let's say I would like to retrieve a certain number of titles from a table 'node' (simplified below). I'm using MySQL by the way. node_ID INT(11) NOT NULL auto_increment, node_title VAR...