order-by

Mysql Join and Order

Hello, I have 3 tables that have same columns,but different data ( deposits,withdrawals,transfers ) CREATE TABLE IF NOT EXISTS withdrawals ( id int(11) NOT NULL auto_increment, user varchar(12) default NULL, amount double(12,2) default NULL, date timestamp NULL default CURRENT_TIMESTAMP, time varchar(50) defau...

Mysql: Best practice to get last records

Hi there ! I got a spicy question about mysql... The idea here is to select the n last records from a table, filtering by a property, (possibly from another table). That simple. At this point you wanna reply : let n = 10 SELECT * FROM huge_table JOIN another_table ON another_table.id = huge_table.another_table_id AN...

Hibernate order by with nulls last

Hibernate used with PostgreSQL DB while ordering desc by a column puts null values higher than not null ones. SQL99 standard offers keyword "NULLS LAST" to declare that null values should be put lower than not nulls. Can "NULLS LAST" behaviour be achieved using Hibernate's Criteria API? ...

LINQ query OrderBy doesn't work

_db.InstellingAdressens .Where(l => l.GEMEENTE.Contains(gem_query)) .OrderBy(q => q.GEMEENTE) .Select(q => q.GEMEENTE) .Distinct(); this is the query. it returns a List<string> but the strings are not ordered at all. Why does the OrderBy have no effect? and how to fix it? ...

Complex ordering in SQL Query: possibly using CASE WHEN

I have a complex prioritisation algorithm that I want to write in SQL to when return prioritised pages of data. Some of these are prioritised according to fixed values, other are ordered by variable values. i.e. // Initial sort on ATTR1 (value1/value2 is higher than value3) if ATTR1 = value1 or value2 then orderBy creationDate, then ...

What index should be used for this query AND for the ORDER BY clause?

Hi, I have a MyISAM table T with the following schema: f1 (integer unsigned not null) f2 (integer unsigned not null) This table has an index on f2 and it currently contains 320 million rows, and is expected to grow at the rate of about 200,000 rows once a week. I perform the following query on this table: SELECT DISTINCT T.f1 FROM T ...

SQL Server: ORDER BY in subquery with UNION

i have two queries being combined with a UNION ALL1: --Query 1 SELECT Flavor, Color FROM Friends   --Query 2 SELECT Flavor, (SELECT TOP 1 Color FROM Rainbows WHERE Rainbows.StrangerID = Strangers.StrangerID ORDER BY Wavelength DESC ) AS Color FROM Strangers Both of which, of course, work fine separately, but ...

What to prefer in query optimization: Using filesort or more rows examined

Hello all I'm trying to optimize this mysql query using EXPLAIN. Can somebody please help me out over here? EXPLAIN SELECT * FROM keyword WHERE keyword LIKE "panasonic%" AND keyword != "panasonic" AND price < 3230 AND price > 3370 ORDER BY price DESC LIMIT 99 Basically I want to find out the keywords which start with "some keyword" b...

show the posts category order by month in wordpress

hi guys, today i solve the problem, how to show posts categories by month like as following url: http://www.onlinescratchcards.me.uk/blog/ ...

Django Query using .order_by() and .latest()

I have a model: class MyModel(models.Model): creation_date = models.DateTimeField(auto_now_add = True, editable=False) class Meta: get_latest_by = 'creation_date' I had a query in my view that did the following: instances = MyModel.objects.all().order_by('creation_date') And then later I wanted instances.latest(), but ...

hibernate : howto order by custom (user-defined) function in PostgreSQL 8.4

We have a Family Hibernate Entity. This entity has(among others) 4 booleans properties. When retrieving the Families from the Postgres 8.4 DB, it is required that the List of families be ordered by sum of the boolean properties. There are 2 other fields in the order by criterion. Eg select fam.a, fam.b, fam.c, fam.d, fam.e from family...

Using LINQ-to-NHibernate Sort an IQueryable of T based on a property value within T's Children

Though the project in which I'm having this issue addresses a different domain, I've found a metaphor that may help to present my question clearly: Let's say that you had to develop a Forum web site, and it was a requirement that the default view for the site needs to display Forum Threads ordered by the threads with the most recent For...

Force Specific Record to Top When Performing GROUP BY

I have the following MySQL query and tables from which I am querying: SELECT `Song`.`id`, `Song`.`file_name`, `User`.`first_name`, `Vote`.`value`, Sum(`Vote`.`value`) AS score FROM `songs` AS `Song` LEFT JOIN votes AS `Vote` ON (`Song`.`id`=`Vote`.`song_id`) LEFT JOIN `users` AS `User` ON (`Song`.`user_id` = `User`.`id`) GROUP BY `...

How to convert the column in order by linq

Select Year From MyTable Order By Cast( [Year] as Int ) Desc Same thing I am trying to do in the linq order by. It's not working. I have column that is defined in the data base as string (Varchar) and I need to cast/convert it to integer before I need to sort it. What should be my linq statement? Thanks in advance. ...

Is it possible to remove order from Hibernate Criteria?

If I have an @OrderBy("someProperty") annotation on an object and then use a Criteria to add an ORDER BY clause like so: criteria.addOrder(Order.asc("id")); The resulting SQL will do the ordering like this: ORDER BY someProperty, id asc Is it possible to change the order of the two or to remove the someProperty order? I can't r...

Reverse the "natural order" of a MySQL table without ORDER BY ?

I'm dealing with a legacy database table that has no insertion date column or unique id column, however the natural order of insertion is still valid when examined with a simple SELECT * showing oldest to newest. I'd like like to fetch that data with pagination but reverse the order as if it was ORDER BY date DESC I've thought about wr...

Combine SQL with PHP

$months = array('January', ... , 'December'); $sql=' SELECT * FROM `residencies` WHERE `Year` = 2010 ORDER BY array_search($months,`MonthFrom`) `DayFrom`'; Doesn't work (i already feared that), it's supposed that the elements are sorted by first the position of MonthFrom in the array $months, and those who have the same position should...

mysql: where write order clause in join stuctures?

Let's assume i have three table - list,a and b table list |a_id|--|b_id|--'name' |4 |--|3 |--|foo| |1 |--|2 |--|foo| table a |id|--|name|--|order| |1 |--|a_1 |--|1 | |2 |--|a_2 |--|2 | ..................... |n |--|a_n |--|n | table b |id|--|name|--|order| |1 |--|b_1 |--|1 | |2 |--|b_2 |--|2 | ......................

mysql get table column names in alphabetical order

Is it possible to query a MySQL database to get the column names of a table in alphabetical order? I know that SHOW COLUMNS `table_name`; or DESCRIBE `table_name`; will give me a list of the columns in a table (along with other info), but is it possible to alter the query in order to get the columns sorted alphabetically. Adding O...

php + mysql, order by name + starting at specific id

MySQL: id | name | ------------ 1 | Joe | 2 | Craig | 3 | Shawn | 4 | Ryan | 5 | Seth | PHP: $a = mysql_query("SELECT * FROM table_name ORDER BY name DESC"); what I want to do though is, I want to start at id: 3, so it should output: 3,4,5,1,2 ...