order-by

MySQL order by rand() grouped by day

Is it possible to get random items inside the same day? For example: +----+---------------------+ | id | md | +----+---------------------+ | 1 | 2010-06-27 11:26:01 | | 2 | 2010-06-27 11:28:20 | | 3 | 2010-06-27 11:29:46 | | 4 | 2010-06-27 11:30:50 | | 5 | 2010-06-27 12:20:56 | | 6 | 2010-06-...

MySQL - Specialized Order By?

Hello. Is there a way to specify a sorting procedure for ORDER BY, or some kind of custom logic? What I need is to check some other data for the column being ordered, which is also in the row. For example if one column has a higher value than another, but a certain condition isn't met, it's sorted as lower. Right now I pull all the dat...

C# MVC: Func<Table1, "runtime type"> How do I get a dynamic type?

Hey, I'm trying to sort a custom data grid of columns based on what the user clicked. The variable "sort" is being passed to the controller but is just a string saying what column is to be sorted. I need to get the Type of that column to be used in a LambdaExpression... heres the code ParameterExpression param = Expression.Parameter(ty...

SQL Query always uses filesort in order by clause

Hello all I am trying to optimize a sql query which is using order by clause. When I use EXPLAIN the query always displays "using filesort". I am applying this query for a group discussion forum where there are tags attached to posts by users. Here are the 3 tables I am using: users, user_tag, tags user_tag is the association mapping ...

NHibernate Subquery with OrderBy

Hi, I have 2 entities... Audio Tag These are setup as a many-to-many, Audio has a reference to Tags, but Tags doesn't have a reference back to Audio. What i want to do is return all tags matching a search query with a count of how many Audios for each tag, they also need to be ordered by the Tags with the most Audio items. Just to m...

HSQLDB 1.8 not able to do order by in derived tables?

Hi, I have a query where I am doing an Order by inside of a derived table that is being inner joined. Ex: SELECT g.* FROM ( SELECT ... FROM ... ORDER BY alias.some_column LIMIT 0, 20 ) as g ... # other joins This works fine in MySQL, but it fails in HSQLDB. The reason I put the order by here is that mysql is much fa...

Row_Number() CTE Performance when using ORDER BY CASE

I have a table I'd like to do paging and ordering on and was able to get a query similar to the following to do the work (the real query is much more involved with joins and such). WITH NumberedPosts (PostID, RowNum) AS ( SELECT PostID, ROW_NUMBER() OVER (ORDER BY CASE WHEN @sortCol = 'User' THEN User END DESC, CASE ...

PHP beginner: How to "Order by" that query?

Hello, I have a rather (I think) easy problem to solve but being a PHP newbie, I can't seem to get the answer... The query below, print a cloud of tags on my page. I'd like to add "ORDER BY TagID ASC" to this query so that the tags appeared according to their ID #, but I have no idea, where to put it (I didn't write that query, obvious...

Order by Maximum condition match

Please help me to create a select query which contains 10 'where' clause and the order should be like that: the results should be displayed in order of most keywords(where conditions) matched down to least matched. NOTE: all 10 condition are with "OR". Please help me to create this query. i am using ms-sql server 2005 Like: Select * ...

Can LINQ order by a column index?

Hi, I'm struggling to find LINQ orderby examples, where data is ordered by a column index. Is this possible to do? Thank you ...

Order items by date string in PHP?

I'm working on WordPress theme which has some custom functionality which includes the ability to add "events" that include a start and/or end date in string format. I'm wondering what the best method would be to parse these date strings in order to figure out which event should be shown in what order? Here's an example of the date stri...

OrderBy("it." + sort) -- Hard coding in LINQ to Entity framework?

Hi All I have been trying to use dynamic LINQ to Entity in my application for specifying the OrderBy attribute at runtime. However when using the code as described in the majority of documentation: var query = context.Customer.OrderBy("Name"); I received the following exception: System.Data.EntitySqlException: 'Name' could not be re...

T-Sql - Order By on Alphanumeric

Hi folks, i have a list of alphanumeric tokens, say '1a', '1b', '02', '03', '10', '11', etc... Now, what's the best way to do an order by on this list of tokens? I am getting '1a', '1b', '10', '11', '02', '03', but i need it to be '1a', '1b', '02', '03', '10', '11' UPDATE ok, i am doing this after the suggestion but it's not w...

Passing an Orderby

I have a list view with several columns; things such as name, date, etc. I want to be able to click on the header TextView and sort the list by that field. When the list loads the variable works, and a list is queried and sorted by the field _id (no surprise other than it works), but when i click on the header TextView i get a force clos...

How to avoid OrderBy - memory usage problems

Let's assume we have a large list of points List<Point> pointList (already stored in memory) where each Point contains X, Y, and Z coordinate. Now, I would like to select for example N% of points with biggest Z-values of all points stored in pointList. Right now I'm doing it like that: N = 0.05; // selecting only 5% of points double cu...

MySql - Order by date and then by time

Hi! I have a table with 2 'datetime' fields: 'eventDate' and 'eventHour'. I'm trying to order by 'eventDate' and then by 'eventHour'. For each date I will have a list os events, so that's why I will to order by date and then by time. thanks!! ...

ORDER BY not working in MySQL 5.1

Hello, guys. I have a problem with sql query in php: select user, name, outlet, switch, port, vlan, mac, status from access where user like '%' and name like '%' and outlet like '%' and switch like '%' and port like '%' and vlan like '%' and mac like '%' and status like '%' order by 'user'; When r...

ORDER BY in PHP/MYSQL not working

The sql query is $query = mysql_query( "SELECT * FROM games ORDER BY gamename" ); But it doesn't order them by the gamename. This is a sample of the database id (int) | gamename (text) | gameurl (varchar) | width (int) | height (int) 1--------- Copter ---------- copter ----------- 100 ---------- 200-------- 2--------- Adrenaline -...

SQL: Any straightforward way to order results FIRST, THEN group by another column?

I see that in SQL, the GROUP BY has to precede ORDER BY expression. Does this imply that ordering is done after grouping discards identical rows/columns? Because I seem to need to order rows by a timestamp column A first, THEN discarding rows with identical value in column A. Not sure how to accomplish this... I am using MySQL 5.1.41 ...

LINQ OrderBy query

Hello all. I have the following that pulls through a list of suppliers: public List<tblSupplierPerformance> GetSupplierInfo(string memberid, string locationid, string supplieridname) { MyEntities suppliers = new MyEntities(); var r = (from p in suppliers.tblSupplierPerformances where p.MemberId == memberid && p.Lo...