order-by

Ordering By Mapping Table Value in Hibernate

I have a @ManyToMany mapping where the table self-references through a mapping table, and we want to order on an order id in the actual mapping table, but are finding it difficult to configure this. We could perform it in hibernate xml, so it is natural to assume the support is there in JPA annotations. Does anybody know how we can orde...

hibernate order by association

I'm using Hibernate 3.2, and using criteria to build a query. I'd like to add and "order by" for a many-to-one association, but I don't see how that can be done. The Hibernate query would end up looking like this, I guess: select t1.a, t1.b, t1.c, t2.dd, t2.ee from t1 inner join t2 on t1.a = t2.aa order by t2.dd <-- need to add this ...

Mysql ORDER BY using date data row

I have a query something like this: SELECT title, desc, date FROM tablename ORDER BY date ASC, title ASC; Works fine when the data actually has a date. Issue is, date submission is optional, so I sometimes get 0000-00-00 as a date, which has the unfortunate effect of placing all nondated rows on top. So, I then tried this: S...

Slow query when using ORDER BY

Here's the query (the largest table has about 40,000 rows) SELECT Course.CourseID, Course.Description, UserCourse.UserID, UserCourse.TimeAllowed, UserCourse.CreatedOn, UserCourse.PassedOn, UserCourse.IssuedOn, C.LessonCnt FROM UserCourse INNER JOIN Course USING(CourseID) INNER JOIN ( SELECT CourseID, COUNT(*) AS Le...

Default row ordering for select query in oracle

In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified. Is it the order in which the rows were inserted there is no default ordering at all none of the above. ...

What's the syntax for ORDERBY in VB.NET's LINQ?

I'm new to Linq, what's the syntax for orderby in VB? Dim cxt As New datContext Dim qry = (From lst In cxt.zipcodes _ Select lst.state).Distinct qry = qry.OrderBy() my simple sql statement will be like this: Select distinct state from zipcodes order by State ...

case insensetive order by in SQLite in .net

I'm using SQLite from a C# program using SQLite.net ( http://sqlite.phxsoftware.com ). By default SQLite select order by clause sort is case sensitive, I want the result to sort case insensitive, I found "COLLATE NOCASE" but the documentation says it will only handle English characters in the ascii range, I want true linguistic internat...

MySQL Group By ordering

Hi all, I have the following table: id time text otheridentifier ------------------------------------------- 1 6 apple 4 2 7 orange 4 3 8 banana 3 4 9 pear 3 5 10 grape 2 What I want to do is select ...

condensing query using SQL

I have a table with 4 things I want... the Name, Price, QTY, and a specific Date There are lots of Entries per date: Name Price Date Twin Private $25 06/02/09 Double $35 06/02/09 Single $20 06/02/09 Twin Private $25 06/03/09 Double $35 06/03/09 Single $20 06/03/09 Twin Private ...

MySQL - ORDER BY values within IN()

Hey -- I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function. INPUT: SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C'); OUTPUT: | id | name | ^--------^---------^ | 5 | B | | 6 | B | | 1 | D | | 15 | E | | ...

How to retrieve 10 latest DISTINCT IP's from a MySQL table?

Hey guys, I have a table containing pagehit (normalized) data and I need to grab the 10 latest unique ips. I tried to do it like this: SELECT * FROM spy_hits ORDER BY date desc GROUP BY ip LIMIT 10; Which should give me this result: +-----+------------+-----+---------+----+------+------+---------+-------+-------+ | id | date ...

Use own IComparer<T> with Linq OrderBy

I have a generic List<MyClass> where MyClass has a property "InvoiceNumber" which contains values like: 200906/1 200906/2 .. 200906/10 200906/11 200906/12 My list is bound to a BindingList<T> which supports sorting with linq: protected override void ApplySortCore( PropertyDescriptor property, ListSortDirection directi...

How does OrderBy in LINQ works (behind the scenes)?

I already know that LINQ works by evaluating expressions and iterating one by one thru them (kinf of like a pipeline), however there are certain operations like OrderBy that need to be buffered since sorting needs to analize all the data at once to do the sort. I am interested in knowing behind the scenes how is this data buffered in LI...

MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT emailID, fromEmail, subject, timestamp, MIN(read) as read FROM incomingEmails WHERE toUserID = '$userID' GROUP BY LOWER(fromEmail) ORDER BY timestamp DESC The query almost works as I want it--it s...

ORDER BY date while also using LIMIT in a MySQL query - PHP

HI, I can't quite figure this out, I'm trying to pull records from MySQL, order them reverse-chronologically and limit the results to four per page (and using pagination to organize the pages). It is currently returning this error: Fatal error: SQL in /Users/allan/Sites/4is_site/casestudylist.php on line 126 $limit = 'LIMIT ' .($pag...

Optimizing ORDER BY Query on large table MySQL

I have a browse category query that im trying to optimize. Im ending up with Using temporary; Using filesort in the explain and the query is slow on a category with say 60,000 rows. If i remove the Order By clauses the query runs very fast .05 seconds to do 60,000 rows. With the Order By clauses its very slow around 5 seconds. Parts cont...

Addressing cursors using a field.(SQL Server)

I have a table that I process using a cursor. Lets say it's structure is like this: RID | School | Order | Text Now, I filter out the other schools(so only mine is shown) and then I ORDER BY order, so that I get the text arranged how I want. Now, my problem is, the order isn't straight incrementing(though all of them are unique per scho...

How to change the collation of sqlite3 database to sort case insensitively?

I have a query for sqlite3 database which provides the sorted data. The data are sorted on the basis of a column which is a varchar column "Name". Now when I do the query select * from tableNames Order by Name; It provides the data like this. Pen Stapler pencil Means it is considering the case sensitive stuff. The way...

OrderBy and OrderByDescending are stable?

I am currently reading Pro LINQ c# 2008, and in page 87 the guy says OrderBy and OrderByDescending are stable. But he says exactly the opposite in page 96. It looks to me as he is referring to exactly the same functions, so I don't get it. Are they stable or not? ...

Need lambda expression OrderBy with DateTime conversion

I am trying to create a lambda expression (Linq, C# 3.5) that can perform a OrderBy on a value that is of data type String but which actually contains a parse-able DateTime. For example, typical values may be "5/12/2009" , "1/14/2008", etc. The OrderBy clause below works correctly for ordering (as if string data), but I actually want ...