order-by

Where to insert 'orderby' expression in this linq-to-sql query

var result = db.PhotoAlbums.Select(albums => new PhotoAlbumDisplay { AlbumID = albums.AlbumID, Title = albums.Title, Date = albums.Date, PhotoID = albums.Photos.Select(photo => photo.PhotoID).FirstOrDefault().ToString() }); Wherever I try to put ord...

Ordering of month/year pairs in T-SQL query

I am writing a stored procedure for displaying month and year. It is working, but it is not returning the rows in the desired order. ALTER procedure [dbo].[audioblog_getarchivedates] as begin select DateName(Month,a.createddate) + ' ' + DateName(Year,a.createddate) as ArchiveDate from audio_blog a group by DateName(Month,a.creat...

Mysql Groupby and Orderby problem

Here is my data structure when i try this sql select rec_id, customer_id, dc_number, balance from payments where customer_id='IHS050018' group by dc_number order by rec_id desc; something is wrong somewhere, idk I need rec_id customer_id dc_number balance 2 IHS050018 DC3 -1 3 IHS050018 52 ...

MySQL: Order by field size/length

Here is a table structure (e.g. test): __________________________________________ | Field Name | Data Type | |________________|_________________________| | id | BIGINT (20) | |________________|_________________________| | ti...

Sqlite3 and PDO problem with ORDER BY

Hi, I try to use the SQL statement SELECT * FROM table ORDER BY column via an PDO-Object in PHP. Problem is, that I always get an error (Call to a member function fetchall() on a non-object - that means, the query did not return a PDO-object) when using the names of all columnname EXCEPT for ID. When I query SELECT * FROM table ORDE...

Can someone recommend a good tutorial on MySQL indexes, specifically when used in an order by clause during a join?

I could try to post and explain the exact query I'm trying to run, but I'm going by the old adage of, "give a man a fish and he'll eat for a day, teach a man to fish and he'll eat for the rest of his life." SQL optimization seems to be very query-specific, and even if you could solve this one particular query for me, I'm going to have to...

Order By property from dynamic linq

I am using dynamic linq to make a generic class for processing a generic JqGrid from MVC all works fine (searching, pagination etc) except for sorting on code properties. Sorting works fine when I am hitting the DB to sort the data, but as soon as it is a property I have made the sorting does not work eg public partial class tblStockO...

How to make MYSQL query results ORDER BY conditions order?

My query string is like: SELECT ... FROM maintable LEFT JOIN table1 on (maintable.id = table1.idx) LEFT JOIN table2 on (table1.idy = table2.idy) LEFT JOIN table3 on (table2.idz = table3.idz) WHERE (condition1 OR condition2 OR condition3) AND maintable.status = static //condition1 & condition2 & condition3 are kind of table3.idz = 101, ...

Hibernate: order multiple one-to-many relations

I have a search screen, using JSF, JBoss Seam and Hibernate underneath. There are columns for A, B and C, where the relations are as follows: A (1< -- >) B (1< -- >) C A has a List< B > and B has a List< C > (both relations are one-to-many). The UI table supports ordering by any column (ASC or DESC), so I want the results of the quer...

Order by Domain Extension Name using CodeIgniter Active Record Class

$extension = “SUBSTRING_INDEX(domain_name, ‘.’, -1)”; $this->db->order_by($extension, “asc”); It says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘asc LIMIT 50’ at line 44 But its working when I didn’t used the $this->db->order_by Active Record...

Order by in Postgresql to sort IP address ?

Hi, I've another question I wish to sort a list of IP address. Are there any functions in Postgresql to use with the order by like INET_ATON in MySql ? My current IP is on string format. Thx. Narglix ...

MySQL UNION query from one table + ORDER BY

I have one table with two queries and I need to sort it with descending type using ORDER BY. Here is my MySQL query that does not work properly: (SELECT `text` FROM `comments` WHERE user_fr='".$user."' && archive='1' ORDER BY `is_new_fr` DESC) UNION (SELECT `text` FROM `message` WHERE user_to='".$user."' && archive='1' ORDER B...

MySQL stuck on "using filesort" when doing an "order by"

I can't seem to get my query to stop using filesort. This is my query: SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` FROM `pilots` p LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) AND (s.`pilot` = p.`id`) ) WHERE p.`game` = 1 AND p.`id` <> 2 AND s.`sector` = 43 AND s.`hull` > 0 ORDER BY p.`last_move` DESC Table struc...

select records from table in the order in which i inserted

consider a tale is as follows, EmployeeId | Name | Phone_Number Now, i insert 10 records... When i query them back, select * from myTable they are not selected in the order i inserted. I can obviously keep an autoincrement index and ORDER BY index. But i dont want to alter the table. How can i do this without altering the table? ...

complex sql which runs extremely slow when the query has order by clause

I have following complex query which I need to use. When I run it, it takes 30 to 40 seconds. But if I remove the order by clause, it takes 0.0317 sec to return the result, which is really fast compare to 30 sec or 40. select DISTINCT media.* , username from album as album , album_permission as permission , user a...

How to use orderby in IQueryable object?

How to use orderby in IQueryable object? ...

View and order by

Why can't a subquery of a view have an order by clause?Similarly why one cant change/delete a row through a view when 1)group by is used in view 2)distnct is used in view ...

SQL orderby / case issue: orderdirection fail

I got a stored procedure that delivers a table of students, and it needs to order by surname, name etc... it also needs to sort ascending, descending, depending on the parameter @orderby... code: ORDER BY CASE WHEN @orderby = 'studentkey' THEN Studentkey END ASC, CASE WHEN @orderby = 'studentkey' and @desc = 1 THEN Studentkey END D...

Advantage Database Server ORDER BY behaviour

I'm using ADS v10 beta. I'm trying to numerate ordered resultset. 1) ORDER BY in nested queries. I need to use nested SELECT for some calculations: SELECT Name, Value, ROWNUM() FROM (SELECT * FROM MainTable WHERE Value > 0 ORDER BY Value) a And I'm getting Expected lexical element not found: ) There was a problem parsing the table ...

MySQL ORDER BY offset

In a MySQL query how would I select: all rows, ordered by column name those with name > 'f' should come first, then the rest alphabetically so I'd get G, H, …, X, Y, Z, A, B, C …, F. ...