order-by

SQL query with specialized ordering of results

I have a table that has 3 columns representing the stops in a bus route. ID stop_name stop_order I am wanting to return a list of stops starting with the current stop (which I know). So if the current stop is stop number 5 then what is returned will be as follows: stop_order 5 6 7 8 1 2 3 4 I tried: Select * from routes where stop...

How to optimize an ORDER BY for a computed column on a MASSIVE MySQL table

I have a very large (80+ million row) de-normalized MySQL table. A simplified schema looks like: +-----------+-------------+--------------+--------------+ | ID | PARAM1 | PARAM2 | PARAM3 | +-----------+-------------+--------------+--------------+ | 1 | .04 | .87 | .78 | +-------...

Subsonic 2 Order By sorting not working

Hi, I am trying to sort (ORDER BY ) a subsonic DAL collection New templateController.FetchAll().OrderByDesc("Name") But it is not sorting. Am I missing something? ...

Linq: Grouping a list, sorting it and getting top x values?

I have a list: Name Country Value John Ireland 100 Mary UK 200 Peter Germany 300 Bob UK 100 Pat France 400 I need to Group the list by the country, then sort by the summed values DESC then take the top X values e.g. List.TopX(3) would return France 400 UK 300 Germany 300 ...

SELECT DISTINCT and ORDER BY

If I place DISTINCT keyword i get an error other wise it is working fine. ERROR: Msg 145, Level 15, State 1, Procedure SP_Products_GetList, Line 15 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. ALTER PROCEDURE [dbo].[SP_Products_GetList] @CatID int, @CatName int, @IsNew bit, @InActive bit, @SortBy ...

Rails order by associated object number

Hey, The title of this question might be a bit off but its the closest I could get to what I am trying to do. I have a Products model which has_many Comments. I am looking to come up with a way to grab the top 10 Products with the most comments. Is this possible? At the moment I have: Product.find(:all, :limit => 10) This gets me...

What is the purpose of Order By 1 in SQL select statement?

I'm reading through some old code at work, and have noticed that there are several views with an order by 1 clause. What does this accomplish? Example: Create view v_payment_summary AS SELECT A.PAYMENT_DATE, (SELECT SUM(paymentamount) FROM payment B WHERE = PAYMENT_DATE = B.PAYMENT_DATE and SOME C...

Is there a way in MySQL to choose an order for the beginning and end of rows returned?

Look at this simple query SELECT id FROM users Now, say I wanted to have users with ids ordered 4, 5, 7, 8, then I don't care about the middle, and finally I want the last ones to be 55, 56, 58. So the returned rows would be something like this (\n a new row) 4 // explicitly chose these as the start rows 5 // " " 7 // " " 8 // "...

SQL: GROUP BY records and then get last record from each group ?

I have a table like this: id| name | attendence 1 | Naveed| 1 2 | Naveed| 1 3 | Adil | 1 4 | Adil | 1 I use following query: SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name The result with above query: id| name | attendence 3 | Adil | 1 1 | Naveed | 1 Question: Above result group rows by name but show first row f...

Reorder the results of a mySQL query using PHP without performing another query

I don't know if this is possible. I display a table of results and want to be able to order the table by any of the columns. I don't need help making such a table; I just need information about reordering the results of a query without repeating it with a different ORDER BY instruction. ...

MySQl GROUP BY and ORDER BY not doing what I want

I want to sort my results before grouping them, but I can't seem to get it done. I have this query: SELECT DISTINCT product.id, product_number_value.att_value AS product_number, color_number_value.option_code AS color_number, size_value.option_code AS size_code, size_value.option_position AS size_position FROM product INNER JOIN...

How to sort a collection based on a subcollection property

I would like to sort a collection based on a subcollection property. //the subcollection public class Salary { public int SalaryId {get;set;} public int SalaryYear {get;set;} public double SalaryValue {get;set;} //this is the field we want to sort the parent collection "Person" } //the main collection public class Person { ...

Does table1 UNION ALL table2 guarantee output order table1, table2?

SELECT a FROM b UNION ALL SELECT a FROM c UNION ALL SELECT a FROM d Does UNION ALL guarantee to print out records from tables b, c, d in that order? I.e., no records from c before any from b. This question is not for a specific DBMS. ...

MySQL: ORDER BY whateverField ASC [with row X put first]?

How can I order my resultset but also have a specific row (with fieldX = Y) be the first one? ...

How does MySQL behave in JOIN cases where ORDER BY and LIMIT are specified and only a small number of rows need actually be JOINed?

Suppose I have the following tables: CREATE TABLE Game ( GameID INT UNSIGNED NOT NULL, GameType TINYINT UNSIGNED NOT NULL, PRIMARY KEY (GameID), INDEX Index_GameType (GameType, GameID) ) ENGINE=INNODB CREATE TABLE HighScore ( Game INT UNSIGNED NOT NULL, Score SMALLINT UNSIGNED, PRIMARY KEY (Game), INDEX ...

mysql order by field( with a twist)

I have a product table with a product name, two product attributes fields, and a price field. The problem is that I can't change the way the database is structured. Each attribute fields are equivalent and either can be used. basically : NAME | ATTRIBUTE_1 | ATTRIBUTE_2 | PRICE Tshirt | red | small | 25 Tshirt...

Using First() with OrderBy and DynamicQuery in One-To-Many related tables

Note: I realize this question is similar to another question, however that question was asked in the context of Genom-e and remains unanswered. My question is in the context of LINQ DynamicQuery. I'm using the String extension method overload for OrderBy provided by System.Linq.Dynamic. Passing a String Literal to OrderBy works g...

How to Combine and sort columns? MySQL, CF8, MS Access 2003

I want to produce an alphabetized list of names produced by adding together two columns of names, columnA, and columnB. I have tried this: <cfquery name="listAuthors" datasource="hhLibrary"> SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB FROM books WHERE ColumnB LIKE '#firstletter#%' ORDER BY ColumnB <...

Select top statement ordering by non-unique column

I have two sql queries select * from table1 ORDER BY column1 Select top 10 * from table1 ORDER by column1 Column1 is a non unique column and table1 does not have a primary key. When I run both queries, I'm getting the rows returning in different orders. I attribute this to the fact that the criterion for ranking (the Order By) is no...

Using union and order by clause in mysql

I want to use order by with union in mysql query. I am fetching different types of record based on different criteria from a table based on distance for a search on my site. The first select query returns data related to the exact place search . The 2nd select query returns data related to distance within 5 kms from the place searched. T...