Sql sorting by multiple fields. Sorting data in SQL (ORDER BY)

When fetching data, it can be important to get them in a certain ordered form. Sorting can be performed by any field with any data type. This can be ascending or descending sort for numeric fields. For character (text) fields, this can be an alphabetical sort, although in essence it is also an ascending or descending sort. It can also be performed in any direction - from A to Z, and vice versa from Z to A.

The essence of the sorting process is to bring the sequence to a certain order. You can learn more about sorting in the article "Sorting algorithms" For example, sorting an arbitrary numerical sequence in ascending order:

2, 4, 1, 5, 9

should result in an ordered sequence:

1, 2, 4, 5, 6

Likewise, when sorting ascending string values:

Ivanov Ivan, Petrov Peter, Ivanov Andrey

the result should be:

Ivanov Andrey, Ivanov Ivan, Petrov Petr

Here the line "Ivanov Andrey" went to the beginning, since the comparison of strings is performed character by character. Both lines begin with the same characters "Ivanov". Since the character "A" in the word "Andrey" comes earlier in the alphabet than the character "I" in the word "Ivan", this line will be placed earlier.

Sorting in a SQL Query

To perform sorting, the ORDER BY command must be added to the query string. After this command, the field by which the sorting is performed is indicated.

For examples, we use the goods table:

num (Item Number)	title (title)	price (price)
1	Mandarin	50
2	Watermelon	120
3	A pineapple	80
4	Banana	40

The data is already sorted by the "num" column. Now, let's build a query that will display a table with products sorted alphabetically:

SELECT * FROM goods ORDER BY title

SELECT * FROM goods - indicates to select all fields from the goods table;

ORDER BY - sorting command;

title - the column to sort by.

The result of executing such a query is as follows:

num	title	price
3	A pineapple	80
2	Watermelon	120
4	Banana	40
1	Mandarin	50

You can also sort for any of the table fields.

Sorting direction

By default, ORDER BY sorts in ascending order. To manually control the sort direction, the keyword ASC (ascending) or DESC (descending) is specified after the column name. Thus, in order to display our table in descending order of prices, you need to set the query like this:

SELECT * FROM goods ORDER BY price DESC

Sorting in ascending order of price will be:

SELECT * FROM goods ORDER BY price ASC

Sort by multiple fields

SQL allows sorting by several fields at once. To do this, after the ORDER BY command, the required fields are separated by commas. The order in the result of the query will be adjusted in the same order in which the sort fields are specified.

column1	column2	column3
3	1	c
1	3	c
2	2	b
2	1	b
1	2	a
1	3	a
3	4	a

Let's sort the table according to the following rules:

SELECT * FROM mytable ORDER BY column1 ASC, column2 DESC, column3 ASC

Those. the first column is ascending, the second is descending, the third is again ascending. The query will order the rows by the first column, then, without breaking the first rule, by the second column. Then, in the same way, without violating the existing rules, according to the third. The result will be the following dataset:

column1	column2	column3
1	3	a
1	3	c
1	2	a
2	2	b
2	1	b
3	1	a
3	1	c

Order of ORDER BY in a query

Sorting of rows is most often carried out together with a condition for data selection. The ORDER BY command is placed after the WHERE selection condition. For example, we select products with a price less than 100 rubles, sorting by name in alphabetical order:

SELECT * FROM goods WHERE price 100 ORDER BY price ASC

In the future, we may need to sort our selection - alphabetically for text, or ascending / descending - for numerical values. For such purposes in SQL there is a special operator ORDER BY .

1. Sort the selected data.

Let's sort our entire table by the amount of product sales, namely by the column Amount.

SELECT * FROM Sumproduct ORDER BY Amount

We see that the query sorted the records in ascending order in the field Amount... It is imperative to follow the sequence of the operators, i.e. operator ORDER BY should go at the very end of the request. Otherwise, you will receive an error message.

Also a feature of the operator ORDER BY is that it can sort data by a field that we did not select in the query, that is, it is enough for it to be in the database at all.

2. Sorting by several fields.

Now let's sort our example additionally by one more field. Let it be a field City, which displays the place of sale of products.

SELECT * FROM Sumproduct ORDER BY Amount, City

The sort order will depend on the order of the fields in the request. That is, in our case, first the data will be sorted by the column Amount and then by City.

3. Direction of sorting.

Although the default operator ORDER BY sorts in ascending order, we can also write descending sorts of values. To do this, at the end of each field we put down the operator DESC (which is short for DESCENDING).

SELECT * FROM Sumproduct ORDER BY Amount DESC, City

In this example, the value in the field Amount were sorted in descending order, and in the field City- Ascending. Operator DESC applies to only one column, so, if necessary, it must be written after each field that takes part in the sorting.

Good day members of the forum.
In the process of designing a database for the production of scheduled repairs, it became necessary to calculate the maximum from the dates of several fields, i.e. calculate the latter.
it is easy to do in MS Exel, but how can it be implemented in Access? do you really need to write a long if to compare values ​​between themselves more / less as in the example?
If there is another way, can I post an example.
Thanks in advance.

Answer:

Message from glsn

calculate maximum from dates of multiple fields

If exactly the fields one entry then as in the example. But then your base is most likely not normalized, usually they look for a maximum of one field in several records.

Answer:

again, explain what subselect means to you? This:

update table set attr1 = subselect.attr1, attr2 = subselect.attr2 from table INNER JOIN (SELECT oid, attr1, attr2 FROM Table2) AS subselect on table .oid = subselect.oid

???

Question: ORDER BY sorting by multiple fields

I am creating a forum and I need to sort the data by two fields: zak and date.
If zak = 1, then the topic is at the top and reacts to the date value.
If zak = 0, then the theme only responds to ORDER BY `date` DESC.
P.S. Don't hit the shovel :)

Answer: Then so:
order by zak desc, date desc

Question: Order by by field with values ​​1,1a, 1b, 2,2a, 3 and so on ..

Good day!
Please help a newbie:

There is a field named "noname" in the "name" table with values ​​containing a number and a letter -
Select noname from name order by noname;
Gives a result like this:

"1"
"10"
"10a"
"10c"
"10d"
"11"
"11a"
"11b"
"12"
"13"
"14"
"14b"
"14c"
"14d"
"15"
"16"
"17"
"18"
"19"
"1a"
"1b"
"1c"
"2"
"20"
"20a"
"20b"
"21"
"21a"
"21b"
"21c"
"21d"
"21e"
"22"
"23"
"23b"
"23c"
"24"
"24b"
"25"
"26"
"27"
"27a"
"3"
"30"
"31"
"32"
"3a"
"4"
"40a"
"40b"
"41"
"41b"
"41c"
"42a"
"43"
"43b"
"43c"
"43d"
"44"
"45"
"45a"
"45b"
"45c"
"45d"
"4b"
"4c"
"4d"
"4e"
"5"
"5a"
"6"

How to make order by so that sorting is first by number and then by letter, that is -

1
1a
1b
1c
2
3
3a
4
4b
4c
4d
etc..

Answer: Novichek_nes,
very similar to HEX
ORDER BY DECODE (LPAD (noname, 16, "0"), "HEX")

Question: Sorting by multiple fields depending on the condition

Tell me how best to sort by several fields and by ascending / descending order, depending on the conditions?

just a few columns, everything is simple

Case @ sortcolumn1
when "column1" then column1
when "column1" then column2
end
case @ sortcolumn2
when "column1" then column1
when "column1" then column2
end

but how to add another ASX / DESC option?

Answer: In such cases, it is more efficient to sort by the client application.

Loading using SQL Loader (Sqlldr) from one file to several fields of the database.
Example task:
The file contains the fields - field1; field2; field3
There is a table with fields - field1; field2; field3; field4 - where when loading data from our file, the same values ​​should be obtained in the second and fourth fields - field2 = field4.
The fields are of different lengths.
How do I write a control file to load data in this case?

Question: counting the number of records in several fields

Can you please tell me how to count on several fields? Let's say you have a table Car with fields Production_Year and Make. You need to make a parametric input of values ​​and calculate how many cars of the specified brand of the specified year of manufacture are in the table.
Thank you.

Answer: exhibit, all counts should be done by the record code, the combination of Mark-year fields in the table should be indexed

Question: How to get sequence number (but not ID) in a query with ORDER BY

Good day,

Tell a newbie:

Let's say you have a simple table:

CREATE TABLE List (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name VARCHAR (100), City VARCHAR (100)) CREATE INDEX NameIdx ON List (Name); CREATE INDEX CityIdx ON List (City);

And there are many (say, a million) entries in it. Simple pagination through OFFSET x LIMIT 500 is used to display the table.

If the table is sorted by ID, then everything is clear - the new record will be on the last page. But what if the user chose to sort by the Name field before adding the record? How, in this case, to understand which page it is on, i.e. what offset should be in OFFSET? And if you choose to sort by two fields - ORDER BY Name, City - how to understand on which page the added record should be without going through the entire query?

Roughly speaking, you need to get COUNT (*) of the number of records that are BEFORE the newly added one, taking into account sorting by fields.

Thanks for the help!

Answer:
The field by which sorting is enabled in the grid does not matter in the new record ... But does it have records in other, more important fields? At least ID? Can you even find the newly added record in the database? Hint: read about AUTOINCREMENT

AND?
< "jonny" and City >"Moscow"

and for an empty city:
select count (*) from table where Name< "jonny" and City is null И делай себе соответствующие условия больше-меньше или null в зависимости от того какая там у тебя сортировка включена и что в искомой строке задано.

Well, if you really want to, then of course you can sort it out ... Users will grumble and spit, but this is their problem, right?

There is such an idea, the main informative data in the table
--this is full name
--and several fields with phone numbers associated with the full name
- (other fields are regions, operators, and other information).

In the database, you need to make a selection to search for a specific phone number among all full names, and display all information on it (phone number, all full name where it occurs, region).

Question: how to organize records of several phone numbers for more convenient search and display of information
- I have 6 fields with phone numbers for each full name
- and how to form a request to search for a specific number using several fields with phone numbers?

Answer: your tables are not normalized

Tab1 is related to tab2 as 1: M
--id1
--Full name
--idcity

Tab2
--id2
--id1
--net
--connection
--room

CITY DIRECTORY
--idcity
--Name
--idRegion

Directory Regions
--idRegion
--Name

When fetching data, it can be important to get them in a certain ordered form. Sorting can be performed by any field with any data type. This can be ascending or descending sort for numeric fields. For character (text) fields, this can be an alphabetical sort, although in essence it is also an ascending or descending sort. It can also be performed in any direction - from A to Z, and vice versa from Z to A.

The essence of the sorting process is to bring the sequence to a certain order. You can learn more about sorting in the article "Sorting algorithms" For example, sorting an arbitrary numerical sequence in ascending order:

2, 4, 1, 5, 9

should result in an ordered sequence:

1, 2, 4, 5, 6

Likewise, when sorting ascending string values:

Ivanov Ivan, Petrov Peter, Ivanov Andrey

the result should be:

Ivanov Andrey, Ivanov Ivan, Petrov Petr

Here the line "Ivanov Andrey" went to the beginning, since the comparison of strings is performed character by character. Both lines begin with the same characters "Ivanov". Since the character "A" in the word "Andrey" comes earlier in the alphabet than the character "I" in the word "Ivan", this line will be placed earlier.

Sorting in a SQL Query

To perform sorting, the ORDER BY command must be added to the query string. After this command, the field by which the sorting is performed is indicated.

For examples, we use the goods table:

num (Item Number)	title (title)	price (price)
1	Mandarin	50
2	Watermelon	120
3	A pineapple	80
4	Banana	40

The data is already sorted by the "num" column. Now, let's build a query that will display a table with products sorted alphabetically:

SELECT * FROM goods ORDER BY title

SELECT * FROM goods - indicates to select all fields from the goods table;

ORDER BY - sorting command;

title - the column to sort by.

The result of executing such a query is as follows:

num	title	price
3	A pineapple	80
2	Watermelon	120
4	Banana	40
1	Mandarin	50

You can also sort for any of the table fields.

Sorting direction

By default, ORDER BY sorts in ascending order. To manually control the sort direction, the keyword ASC (ascending) or DESC (descending) is specified after the column name. Thus, in order to display our table in descending order of prices, you need to set the query like this:

SELECT * FROM goods ORDER BY price DESC

Sorting in ascending order of price will be:

SELECT * FROM goods ORDER BY price ASC

Sort by multiple fields

SQL allows sorting by several fields at once. To do this, after the ORDER BY command, the required fields are separated by commas. The order in the result of the query will be adjusted in the same order in which the sort fields are specified.

column1	column2	column3
3	1	c
1	3	c
2	2	b
2	1	b
1	2	a
1	3	a
3	4	a

Let's sort the table according to the following rules:

SELECT * FROM mytable ORDER BY column1 ASC, column2 DESC, column3 ASC

Those. the first column is ascending, the second is descending, the third is again ascending. The query will order the rows by the first column, then, without breaking the first rule, by the second column. Then, in the same way, without violating the existing rules, according to the third. The result will be the following dataset:

column1	column2	column3
1	3	a
1	3	c
1	2	a
2	2	b
2	1	b
3	1	a
3	1	c

Order of ORDER BY in a query

Sorting of rows is most often carried out together with a condition for data selection. The ORDER BY command is placed after the WHERE selection condition. For example, we select products with a price less than 100 rubles, sorting by name in alphabetical order:

SELECT * FROM goods WHERE price 100 ORDER BY price ASC