In short: Is there any way to sort the values in a GROUP_CONCAT statement? Query: GROUP_CONCAT((SELECT GROUP_CONCAT(parent.name SEPARATOR ” » “) FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.id = l.competence AND parent.id != 1 ORDER BY parent.lft) SEPARATOR “<br />\n”) AS competences I get this row: Crafts … Read more
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 CONDITION) AS SUM_X, (SELECT SUM(paymentamount) FROM payment B WHERE PAYMENT_DATE = … Read more
I’ve a table like: +———–+——-+————+ | client_id | views | percentage | +———–+——-+————+ | 1 | 6 | 20 | | 1 | 4 | 55 | | 1 | 9 | 56 | | 1 | 2 | 67 | | 1 | 7 | 80 | | 1 | 5 | 66 | … Read more
Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key* So SELECT * FROM large ORDER BY `id` LIMIT 0, 30 takes far less than SELECT * FROM large ORDER BY `id` LIMIT 10000, 30 … Read more
I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause: SELECT * FROM comments WHERE (comments.id IN (1,3,2,4)); This returns comments in an arbitrary order which in my happens to be ids like 1,2,3,4. I … Read more
I want to order by Time,but seems no way to do that ? mysql> show processlist; +——–+————-+——————–+——+———+——–+———————————-+——————————————————————————————————+ | Id | User | Host | db | Command | Time | State | Info | +——–+————-+——————–+——+———+——–+———————————-+——————————————————————————————————+ | 1 | system user | | NULL | Connect | 226953 | Waiting for master to send event | … Read more
Is it possible to order when the data is come from many select and union it together? Such as Select id,name,age From Student Where age < 15 Union Select id,name,age From Student Where Name like “%a%” How can I order this query by name? I tried this Select id,name,age From Student Where age < 15 … Read more
I’m stuck on a simple task. I just need to order results coming from this call $results = Project::all(); Where Project is a model. I’ve tried this $results = Project::all()->orderBy(“name”); But it didn’t work. Which is the better way to obtain all data from a table and get them ordered? 12 Answers 12
I want to run this query: SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM purchases WHERE purchases.product_id = 1 ORDER BY purchases.purchased_at DESC But I get this error: PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions Adding address_id as first ORDER BY expression silences the error, but I really don’t want to … Read more
There are plenty of similar questions to be found on here but I don’t think that any answer the question adequately. I’ll continue from the current most popular question and use their example if that’s alright. The task in this instance is to get the latest post for each author in the database. The example … Read more
