Questions tagged [sql-order-by]
An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns.
7,672
questions
1817
votes
7
answers
783k
views
Multiple "order by" in LINQ
I have two tables, movies and categories, and I want to get an ordered list by categoryID first and then by Name.
The movie table has three columns ID, Name and CategoryID.
The category table has two ...
- 20.7k
923
votes
9
answers
1.4m
views
SQL multiple column ordering
How can I sort multiple columns in SQL and in different directions? For instance, 'column1' would be sorted descendingly and 'column2' ascendingly.
- 16.6k
512
votes
4
answers
1.1m
views
LINQ Orderby Descending Query
I have a LINQ query that I want to order by the most recently created date.
I tried:
var itemList = from t in ctn.Items
where !t.Items && t.DeliverySelection
...
- 16.6k
382
votes
15
answers
374k
views
SQL how to make null values come last when sorting ascending
I have a SQL table with a datetime field. The field in question can be null. I have a query and I want the results sorted ascendingly by the datetime field, however I want rows where the datetime ...
- 17.3k
342
votes
14
answers
176k
views
MySQL Orderby a number, Nulls last
Currently I am doing a very basic OrderBy in my statement.
SELECT * FROM tablename WHERE visible=1 ORDER BY position ASC, id DESC
The problem with this is that NULL entries for 'position' are ...
- 4,492
339
votes
7
answers
356k
views
PostgreSQL DISTINCT ON with different ORDER BY
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:
...
- 5,176
309
votes
4
answers
455k
views
Order a MySQL table by two columns
How do I sort a MySQL table by two columns?
What I want are articles sorted by highest ratings first, then most recent date. As an example, this would be a sample output (left # is the rating, then ...
magenta
301
votes
13
answers
445k
views
MySQL order by before group by
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'...
- 7,419
299
votes
12
answers
588k
views
How to order by with union in SQL?
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 &...
- 3,811
293
votes
13
answers
351k
views
Laravel Eloquent: Ordering results of all()
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");
...
- 16.2k
291
votes
8
answers
614k
views
How does MySQL process ORDER BY and LIMIT in a query?
I have a query that looks like this:
SELECT article FROM table1 ORDER BY publish_date LIMIT 20
How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and ...
- 67k
251
votes
17
answers
183k
views
ORDER BY the IN value list
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,074
236
votes
5
answers
211k
views
GROUP_CONCAT ORDER BY
I've a table like:
+-----------+-------+------------+
| client_id | views | percentage |
+-----------+-------+------------+
| 1 | 6 | 20 |
| 1 | 4 | 55 |
| ...
- 2,669
230
votes
8
answers
266k
views
how to customize `show processlist` in mysql?
I want to order by Time,but seems no way to do that ?
mysql> show processlist;
+--------+-------------+--------------------+------+---------+--------+----------------------------------+------------...
- 138k
229
votes
6
answers
112k
views
Why does MYSQL higher LIMIT offset slow the query down?
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 * ...
- 2,293
216
votes
8
answers
268k
views
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....
- 7,775
204
votes
2
answers
110k
views
MySQL: Sort GROUP_CONCAT values
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, ...
- 4,384
198
votes
10
answers
315k
views
SQL order string as number
I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances.
It is taking them as character not as number while sorting.
In database I ...
- 3,848
184
votes
13
answers
163k
views
Ordering by the order of values in a SQL IN() clause
I am wondering if there is away (possibly a better way) to order by the order of the values in an IN() clause.
The problem is that I have 2 queries, one that gets all of the IDs and the second that ...
- 144k
181
votes
7
answers
452k
views
C# list.Orderby descending
I would like to receive a List sorted by Product.Name in descending order.
Similar to the function below which sorts the list in ascending order, just in reverse, is this possible?
var newList = list....
- 6,672
166
votes
14
answers
233k
views
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 ...
- 1,725
165
votes
3
answers
132k
views
How to use SQL Order By statement to sort results case insensitive?
I have a SQLite database that I am trying to sort by Alphabetical order. The problem is, SQLite doesn't seem to consider A=a during sorting, thus I get results like this:
A
B
C
T
a
b
c
g
I want to ...
- 15k
165
votes
14
answers
489k
views
How to use DISTINCT and ORDER BY in same SELECT statement?
After executing the following statement:
SELECT Category FROM MonitoringJob ORDER BY CreationDate DESC
I am getting the following values from the database:
test3
test3
bildung
test4
test3
test2
...
- 7,113
155
votes
12
answers
196k
views
sql ORDER BY multiple values in specific order?
Ok I have a table with a indexed key and a non indexed field.
I need to find all records with a certain value and return the row.
I would like to know if I can order by multiple values.
Example:
id ...
- 84.3k
154
votes
8
answers
191k
views
Ordering by specific field value first
I have a table with 3 columns:
id
name
priority
1
core
10
2
core
9
3
other
8
4
board
7
5
board
6
6
core
4
I want to order the result set using priority but first those rows that have name=core ...
- 4,625
146
votes
4
answers
166k
views
Order a List (C#) by many fields? [duplicate]
I want to order a List of objects in C# by many fields, not just by one. For example, let's suppose I have a class called X with two Attributes, A and B, and I have the following objects, in that ...
- 1,971
144
votes
2
answers
69k
views
Sort NULL values to the end of a table
Is there a way with PostgreSQL to sort rows with NULL values in fields to the end of the selected table?
Like:
SELECT * FROM table ORDER BY somevalue, PUT_NULL_TO_END
- 11.5k
144
votes
5
answers
368k
views
How to use Oracle ORDER BY and ROWNUM correctly?
I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it.
I have queries which returns the most recent record of some tables, based on a ...
- 17.8k
139
votes
6
answers
146k
views
C# Sort and OrderBy comparison
I can sort a list using Sort or OrderBy. Which one is faster? Are both working on same
algorithm?
List<Person> persons = new List<Person>();
persons.Add(new Person("P005", "Janson"));
...
- 5,061
136
votes
3
answers
101k
views
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:
- ...
- 2,525
135
votes
6
answers
146k
views
Order by multiple columns with Doctrine
I need to order data by two columns (when the rows have different values for column number 1, order by it; otherwise, order by column number 2)
I'm using a QueryBuilder to create the query.
If I ...
- 2,451
131
votes
6
answers
100k
views
MySQL - ORDER BY values within IN()
I'm hoping to sort the items returned in the following query by the order they're entered into the IN() function.
INPUT:
SELECT id, name FROM mytable WHERE name IN ('B', 'A', 'D', 'E', 'C');
OUTPUT:...
- 3,856
129
votes
2
answers
73k
views
Sort by column ASC, but NULL values first?
I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated.
But that field is allowed to be empty or null and I want records with null in last_updated come before non-null ...
- 4,657
123
votes
3
answers
186k
views
MySQL query order by multiple items
Is it possible to order by multiple rows?
I want my users to be sorted by last_activity, but at the same time, I want the users with pictures to appear before the ones without
Something like this:
...
- 2,443
120
votes
6
answers
273k
views
MySQL "Group By" and "Order By"
I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this:
SELECT
`timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` ...
- 6,650
118
votes
10
answers
290k
views
SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12
I’m attempting to order by a number column in my database which has values 1-999
When I use
ORDER_BY registration_no ASC
I get….
1
101
102
103
104
105
106
107
108
109
11
110
Etc…
So it appears ...
- 1,189
115
votes
3
answers
97k
views
MySQL: Order by field size/length
Here is a table structure (e.g. test):
Field Name
Data Type
id
BIGINT (20)
title
varchar(25)
Description
Text
A query like:
SELECT * FROM TEST ORDER BY description DESC;
But I would like to ...
- 2,356
110
votes
4
answers
93k
views
Sorting related items in a Django template
Is it possible to sort a set of related items in a DJango template?
That is: this code (with HTML tags omitted for clarity):
{% for event in eventsCollection %}
{{ event.location }}
{% for ...
- 9,266
103
votes
3
answers
120k
views
How to order by column A and then by column B?
How to write SQL so that the result can be ordered first by column A then by column B. Something like below:
SELECT * FROM tbl WHERE predictor ORDER by col_A and ORDER by col_B
- 40.3k
101
votes
12
answers
450k
views
Can I do a max(count(*)) in SQL?
Here's my code:
select yr,count(*)
from movie
join casting on casting.movieid=movie.id
join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr;
Here's the question:
...
- 59.6k
99
votes
9
answers
267k
views
Order results by COUNT without GROUP BY
I have a table and data like this:
ID | Name | Group
1 | Apple | A
2 | Boy | A
3 | Cat | B
4 | Dog | C
5 | Elep | C
6 | Fish | C
and I wish to order it according to the count of items ...
- 1,941
93
votes
4
answers
55k
views
mysql order by, null first, and DESC after
How can I order DESC by a field, but list the NULL values first?
So I'm having a table:
reuestId | offerId | offerTitle
1 | 1 | Alfa
NULL | 2 | Beta
2 | 3 | Gamma
...
- 2,414
92
votes
20
answers
183k
views
MySQL 'Order By' - sorting alphanumeric correctly
I want to sort the following data items in the order they are presented below (numbers 1-12):
1
2
3
4
5
6
7
8
9
10
11
12
However, my query - using order by xxxxx asc sorts by the first digit above ...
- 4,631
89
votes
15
answers
255k
views
SQL Error with Order By in Subquery
I'm working with SQL Server 2005.
My query is:
SELECT (
SELECT COUNT(1) FROM Seanslar WHERE MONTH(tarihi) = 4
GROUP BY refKlinik_id
ORDER BY refKlinik_id
) as dorduncuay
And the error:
...
- 5,872
89
votes
2
answers
73k
views
"Order by" result of "group by" count?
This query
Message.where("message_type = ?", "incoming").group("sender_number").count
will return me an hash.
OrderedHash {"1234"=>21, "2345"=>11, "3456"=>63, "4568"=>100}
Now I want ...
- 43.7k
87
votes
16
answers
192k
views
SQL Query - Using Order By in UNION
How can one programmatically sort a union query when pulling data from two tables? For example,
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY ...
- 4,970
87
votes
5
answers
40k
views
Custom ORDER BY Explanation
I found this some time ago and have been using it since; however, looking at it today, I realized that I do not fully understand why it works. Can someone shed some light on it for me?
ORDER BY s....
- 2,177
86
votes
5
answers
195k
views
Linq order by, group by and order by each group?
I have an object that looks something like this:
public class Student
{
public string Name { get; set; }
public int Grade { get; set; }
}
I would like to create the following query: group ...
- 1,468
82
votes
10
answers
98k
views
SQLite Order By Date1530019888000
Every record in my SQLite database contains a field which contains a Date stored as a string in the format 'yyyy-MM-dd HH:mm:ss'.
Is it possible to query the database to get the record which ...
- 3,211
78
votes
4
answers
48k
views
MySQL ORDER BY IN()
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 ...
- 1,583