order-by

MySQL not using index with JOIN, WHERE and ORDER

We have two tables resembling a simple tag-record structure as follows (in reality it's much more complex but this is the essance of the problem): tag (A.a) | recordId (A.b) 1 | 1 2 | 1 2 | 2 3 | 2 .... and recordId (B.b) | recordData (B.c) 1 | 123 2 | 666 3 | 124...

LINQ - writing a query with distinct and orderby

Hi, I'm quite new to LINQ. Suppose that I had the following table: Incident ID DeviceID Time Info 1 1 5/2/2009 d 2 2 5/3/2009 c 3 2 5/4/2009 b 4 1 5/5/2009 a In LINQ, how could I write a query that finds the most recent and distinct (on Device ID) set of incidents? The result I'd like i...

SQL - Finding differences in row order of two tables

I have two tables of ID's and dates and I want to order both tables by date and see those ids that are not in the same order e.g. table_1 id | date ------------ A 01/01/09 B 02/01/09 C 03/01/09 table_2 id | date ------------ A 01/01/09 B 03/01/09 C 02/01/09 and get the results B C Now...

How do I return rows with a specific value first?

I want my query to return the rows of the table where a column contains a specific value first, and then return the rest of the rows alphabetized. If I have a table something like this example: - Table: Users - id - name - city - 1 George Seattle - 2 Sam Miami - 3 John New York - 4 Amy New York - 5 Er...

orderby statements on multiple joins

Hi, I have three tables, each contain an auto-incrementing PK. I need to select the latest (ie, ORDERBY DESC) entries from the mix of these tables. I'd like to do it in one query, with two joins. My idea was is to somehow select a table, order it by id DESC, then somehow merge the results. Does anyone have a way (or probably a better id...

Ordering on a field in the 'through' Model of recursive ManyToMany relation in Django.

Assuming the following model: class Category(models.Model): related = models.ManyToManyField('self', symmetrical = False, through = 'CategoryRelation', null = True, blank = True) Assuming the following intermediate 'through' relation: class CategoryRelation(models.Model): source = models.ForeignKey('Category', null = False, b...

Datatable select method ORDER BY clause

HI, I 'm trying to sort the rows in my datatable using select method. I know that i can say datatable.select("col1='test'") which in effect is a where clause and will return n rows that satisfy the condition. I was wondering can i do the following datatable.select("ORDER BY col1") ---col1 is the name of hte column I tried datatab...

CASE in ORDER BY few columns

Using a case-then block, I need to choose how to order my SQL 2008 query, by [status] ASC, [date] DESC or just [date] DESC. I know only how to use one column: SELECT * FROM table ORDER BY CASE WHEN @flag = 0 THEN R.[date] END DESC, CASE WHEN @flag = 1 THEN R.[status] END ASC How to use both columns in second CASE? ...

Sub queries and sorting? (ORDER BY)

So my syntax is apparently correct in all three cases (PostgreSQL isn't grousing about anything) but the results come back in the same order with all three of these queries. Even stranger when I add/remove DESC from any of the following it has no impact either. Is it possible to sort results based on elements of a sub query or not? So...

ORDER BY in a Sql Server 2008 view

Hi all... we have a view in our database which has an ORDER BY in it. Now, I realize views generally don't order, because different people may use it for different things, and want it differently ordered. This view however is used for a VERY SPECIFIC use-case which demands a certain order. (It is team standings for a soccer league.) Th...

How to ORDER BY a SUM() in MySQL?

I have a table: "ID name c_counts f_counts " and I want to order all the record by sum(c_counts+f_counts) but this doesn't work: SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIET 20; ...

MySQL ORDER BY IN()

Hi all, I have a PHP array with numbers of ID's in it. These numbers are already ordered. Now i would like to get my result via the IN() method, to get all of the ID's. However, these ID's should be ordered like in the IN method. For example: IN(4,7,3,8,9) Should give a result like: 4 - Article 4 7 - Article 7 3 - Article 3 ...

SQL Query, Selecting 5 most recent in each group

I have this table CREATE TABLE `codes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `language_id` int(11) unsigned NOT NULL, `title` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 lan...

how to do order-by in hibernate mapping

suppose Object A has a list of Object B, and Object B must have a object C, B can be order base on C.level . In A.hbm.xml <bag name="listB" table="T_B" inverse="false" order-by="?? what should i do here???" > <key column="ID_A" not-null="true"/> <many-to-many colum...

SQL Server : alias is ignored in Order by

We have two tables in our application that both have a ShowOrder column.We are using NHibernate in our application and using HQL we join these two tables ordered by ShowOrder of first table and second table respectively. Here's a simplified version of my query : SELECT pr.Id as Id,pr.Title as Title, pr.ShowOrder as ShowOrder FROM Proce...

Order By not working on MySql

Hi Order By not working on MySql the code is as follows, select * from School where School.type = 'HighSchool' order by (select locations.name from locations inner join School_locations on locations.id = School_locations.location_id where School_locations.School_id = School.id and locations.location_country = 'US'...

MySQL Union Query w/ Left Join - Order By Error ?

We have a Union Query. Here's a basic (similar) example: SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "a%" UNION SELECT a.Name, b.Info FROM a LEFT JOIN b ON (a.ID = b.ID) WHERE a.Name LIKE "b%" ORDER BY a.Name, b.Info; I am receiving an error that says "Unknown column 'b.Info' in 'order clause'". When...

How to select first 'N' records from a database containing million records?

I have an oracle database populated with million records. I am trying to write a SQL query that returns the first 'N" sorted records ( say 100 records) from the database based on certain condition. SELECT * FROM myTable Where SIZE > 2000 ORDER BY NAME DESC Then programmatically select first N records. The problem with this approac...

SQL SELECT order by 2 columns and group by

Here're the RS return and the sql issued, SELECT *, (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) AS T FROM games WHERE game_status > 10 ORDER BY status, T; game_id, player_id, start_time, end_time, score, game_status, is_enabled, T 65, 22, '2009-09-11 17:50:35', '2009-09-11 18:03:07', 17, 11, 1, 752 73, 18, '2009-09-11 18...

DynamicLinq OrderBy in n:m relation table

Hi, there is any way to order a query by a many-to-many relation table column in linq? and in scottgu's DynamicLinq? For example: Products: p_id, p_name Product_Order: p_id, o_id, quantity Order: o_id, o_name how can I query for order by Product_Order quantity? from p in model.Products order by p.Product_Order.quantity select p ...