Help:Sorting

From Safer nicotine wiki
Jump to navigation Jump to search

Using sortable tables

When browsing Wikipedia you may encounter tables that have been made sortable. A sortable table is identified by the arrows in one or more of its header cells. Clicking them will cause the table rows to sort in ascending order based on the selected column. A second click on the same arrow will sort in descending order. A third click will restore the original order of the whole table. For example; a third click causes List of countries by intentional homicide rate to reset to its original order by subregion.

The actual sorting process will happen on your computer using client-side JavaScript. For this reason it is only possible to use this functionality if you have JavaScript enabled in your web browser. The sorting process is also dependent on your computer and the amount of data. Sorting a very large table on a slow computer may take a long time.

Example

This is an example of a small sortable table.

name data more data
cats 273 53
dogs 65 8,492
mice 1,649 548

Tables with complex headers

Tables with more complex headers than before now sort correctly. For example:

name data columns another column
data more data
cats 273 53 1
dogs 65 8,492 2
mice 1,649 548 3

Using two or more header rows, the sort arrows are placed on the bottom header row by default. They can be placed a maximum of one row higher by setting class="sorttop" at the top of the bottom header row.

Default

column 1 column 2
3 7
1 3
2 4
{| class="wikitable sortable"
|-
! column 1
! column 2
|-
! style="text-align:left;" | 3
! style="text-align:left;" | 7
|-
| 1
| 3
|-
| 2
| 4
|}

Using class="sorttop"

column 1 column 2
3 7
1 3
2 4
{| class="wikitable sortable"
|-
! column 1
! column 2
|- class="sorttop"
! style="text-align:left;" | 3
! style="text-align:left;" | 7
|-
| 1
| 3
|-
| 2
| 4
|}

Tables with complex data rows

Tables can have cells spanning multiple rows, using |rowspan=n. (See Help:rowspan).

The number of rows must be indicated with each use of rowspan. Before any sorting can be done, the rowspan setup must be correct. An incorrect rowspan organisation breaks sorting option, leaving incorrect data.

See examples below.

When sorted all the rows are filled. Tables without rowspan are much easier to maintain by less experienced editors, and by editors who are stopping by only once to edit the table.

Correct rowspan numbers, with sorting in working order:

name popularity data more data year
cats popular pet 273 53 2013
dogs 65 8,492 2014
mice 1,649 548

Note that, after sorting, the rowspanning cells are cut into rows and their content is repeated (the year "2014" in the example). If the original order of a table is restored by clicking a third time on the same arrow then the cells will remain repeated and not revert to the original rowspan.

Incorrect rowspan numbers breaking sorting, and causing mix-ups in rows and columns:

Bad use of rowspan breaks sorting option
name popularity data more data year
cats popular pet 273 53 2013
dogs 65 8,492 2014
mice 1,649 548

Online table editors and rowspan

There is an easy online wiki table editor here:

It makes it easy to edit the text and links in individual cells of a table. It is especially easy when there are no rowspans in the body of a table. See the previous section. Without rowspans it is easier to change the underlying framework of a table, and move stuff around. Once the wikitext framework is simpler, the online table editor is simpler too, because you don't have to edit the wikitext as much in order to edit the table.

Secondary key

If a column contains a value multiple times then sorting the column preserves the order of the rows within each subset that has the same value in that column (stable sorting). Thus sorting based on a primary, secondary, tertiary, etc. key can be done by sorting the least-significant key first, etc. For example, to sort the table below on the Text column, then the Numbers column, first click on the "Numbers" column heading (the secondary sort key), then the "Text" column heading (the primary sort key).

Another way to sort a table using multiple sort keys is to hold down the shift key while clicking on the column headings for the subsequent sort keys. For example, to sort the table below on the Text column, then the Numbers column, first click on the "Text" column heading (the primary sort key), then hold down the shift key and click on the "Numbers" column heading (the secondary sort key).

Numbers Text Dates Currency More text
4 a 01.Jan.2005 4.20 row 1
5 a 05/12/2006 7.15 row 2
1 b 02-03-2004 5.00 row 3
1 a 03-02-2004 5.00 row 4
2 x 13-apr-2005 row 5
2 a 13-apr-2005 row 6
3 a 17.aug.2006 6.50 row 7
3 z 25.aug.2006 2.30 row 8
3 z 28.aug.2006 5.50 row 9
3 z 31.aug.2006 3.77 row 10
3 z 01.sep.2006 1.50 row 11
25 z 01.sep.2006 1.50 row 12
Bottom

Options for more columns in a narrow screen

Vertical headers

See: Template:Vert header
name
data
more data
another column
cats 273 53 1
dogs 65 8,492 2
mice 1,649 548 3

This template also works with headers that span rows or columns (using rowspan and colspan). Note that there is no vertical bar | between rowspan=2 and {{vert header

name
data columns
another column
data
more data
cats 273 53 1
dogs 65 8,492 2
mice 1,649 548 3
{| class="wikitable sortable"
|-
! rowspan=2 {{vert header|stp=1|name}}
! colspan=2 {{vert header|data columns}}
! rowspan=2 {{vert header|stp=1|another column}}
|-
! {{vert header|stp=1|data}}
! {{vert header|stp=1|more data}}
|-
| cats
| 273
| 53
| 1
|-
| dogs
| 65
| 8,492
| 2
|-
| mice
| 1,649
| 548
| 3
|}

Sorting buttons in a separate row

Note: From an April 2020 discussion someone using a screen reader wrote: "It's still very readable with the empty/clickable row, it's just more annoying to have to go past blank cells; I know they can occur in other circumstances, too. Perhaps this is one of these cases where a minor accessibility improvement loses out for now to better display on screens." There is a Phabricator task T35249 asking that the default location of the sorting icon be below the header text.

To make a table more compact in narrow screens, the sorting buttons can be put in an extra header row below the header cells containing text. The sorting button always ends up in the lowest header cell.

Add a line break <br> or non-breaking space &nbsp; in one of the empty header cells. Otherwise the sorting row will be very narrow. If you are using the data-sort-type="..." attribute, it must be added to the header cell with the sorting icon.

The sorting buttons can be centered with style="background-position: center;" | in each cell. For comparison, this is only done for the last cell below. Here is the table followed by the wikitext for the header cells.

name data columns another column
data more data

cats 273 53 1
dogs 65 8,492 2
mice 1,649 548 3
{| class="wikitable sortable"
|-
! rowspan=2 | name
! colspan=2 | data columns
! rowspan=2 | another column
|-
! data
! more data
|-
! <br>!! !! !! style="background-position: center;" | 
|-

Creating sortable tables

The properties panel of a table in VisualEditor allows you to mark a table as sortable.

Tables can be made sortable via client-side JavaScript by adding class="wikitable sortable" to their top line. These tables need to be properly formatted, with the right number of cells. Additionally you need to make sure that the headers of your column are properly indicated in the wikicode. For this the ! character is used in the table syntax.

If you are using the Visual editor, you can open the properties dialog of a table and select the sortable option.

Simple example

This is the wikisource of the table shown in the first section and shows the typical way to enable table sorting:

{| class="wikitable sortable"
|-
! name
! data
! more data
|-
| cats
| 273
| 53
|-
| dogs
| 65
| 8,492
|-
| mice
| 1,649
| 548
|}

The ! indicates cells that are header cells. In order for a table to be sortable, the first row(s) of a table need to be entirely made up out of these header cells. You can learn more about the basic table syntax by taking the Introduction to tables.

Initial sort order of rows

When users are first presented with a table, the rows will always appear in the same order as in the wikitext. If you want a table to appear sorted by a certain column, you must sort the wikitext itself in that order. This is usually done for the first column. The VisualEditor makes it easy to move individual table columns and rows around. For info about that, and also about putting a table in initial alphabetical order see § Initial alphabetical order.

Restrictions and exclusions

Tables can only click-to-sort vertically downwards (clicking on a topmost-column-name will cause the rows of the table to re-order themselves in their up-and-down positions). It is not possible to click-to-sort horizontally across (there is no way to click on a leftmost-row-cell so as to cause the columns of the table to re-order themselves in their left-to-right positions).

Making selected columns unsortable

If you want a specific column not to be sortable, specify class=unsortable in the attributes of its header cell.

(When using {{vert header}}, disable column sorting by omitting |stp=1 in that template, which overrides anything placed before it.)

Wiki markup

{|class="wikitable sortable"
!Numbers!!Alphabet!!Dates!!Currency!!class=unsortable|Unsortable
|-
|1||Z||02-02-2004||5.00||This
|-
|2||y||13-apr-2005||||Column
|-
|3||X||17.aug.2006||6.50||Is
|-
|4||w||01.Jan.2005||4.20||Unsortable
|-
|5||V||05/12/2006||7.15||See?
|-
!Total: 15!!!!!!Total: 22.85!!
|}

What it looks like in your browser

Numbers Alphabet Dates Currency Unsortable
1 Z 02-02-2004 5.00 This
2 y 13-apr-2005 Column
3 X 17.aug.2006 6.50 Is
4 w 01.Jan.2005 4.20 Unsortable
5 V 05/12/2006 7.15 See?
Total: 15 Total: 22.85

Excluding final rows from sorting

Sometimes it is helpful to exclude the last row of a table from the sorting process. There are two methods to achieve this.

Header as a footer

You want a repeat of the header at the bottom. You do this by using the ! (Exclamation mark) syntax for all cells in the last row of the table. This will be recognized as a footer and the row will not be part of the sorting.

Wiki markup

{|class="wikitable sortable"
!Name!!Surname!!Height
|-
|John||Smith||1.85
|-
|Ron||Ray||1.89
|-
|Mario||Bianchi||1.72
|-
!Name!!Surname!!Height
|}

What it looks like in your browser

Name Surname Height
John Smith 1.85
Ron Ray 1.89
Mario Bianchi 1.72
Name Surname Height

This applies to all rows at the end of the table that are consecutive and fully made up out of header cells.

Plain footer

This can be achieved using class="sortbottom" on the desired table row (line starting with |-).

Wiki markup

{|class="wikitable sortable"
!Name!!Surname!!Height
|-
|John||Smith||1.85
|-
|Ron||Ray||1.89
|-
|Mario||Bianchi||1.72
|- class="sortbottom"
|colspan="2" | Average:||1.82
|}

What it looks like in your browser

Name Surname Height
John Smith 1.85
Ron Ray 1.89
Mario Bianchi 1.72
Average: 1.82

It is possible to keep multiple lines fixed at the bottom, as long as the lines are consecutive. If the original order of a table is restored by clicking a third time on the same arrow then rows with class="sortbottom" will remain at the bottom even if they were not originally at the bottom.

Excluding top rows from sorting

This works the same as above for plain (non-header) rows at the top. This can be achieved using class="sorttop" on the desired table row (line starting with |-). It is possible to keep multiple lines fixed at the top, as long as the lines are consecutive.

Configuring the sorting

By default, the system tries to guess the data type in each column. It does this by looking at the first five rows and evaluating their contents. This process works most of the time but can also easily get confused if you inconsistent values or additional specifiers that the system doesn't know about. To avoid this ambiguity you can force a particular data type or override the value of a cell.

Forcing a column to have a particular data type

The data-sort-type="..." attribute can be added inside the header of a column to ensure that the cells underneath are all treated as a specified type of data. It must go in the header cell with the sorting icon. It will not work in a header cell without a sorting icon. For example; when there are two rows of headers, the bottom row will always have the sorting icons.

The following (case-insensitive) values are valid for data-sort-type:

  • text
  • number
  • currency
  • url for website addresses
  • IPAddress for numeric internet protocol addresses
  • date for language specific standard date format
  • isoDate for dates in ISO 8601 format (e.g. YYYY-MM-DD)
  • usLongDate for dates in the US format (with the month before the day)
  • time

For example:

Without any data-sort-type With data-sort-type=text
{| class="wikitable sortable"
|-
! Album
|-
 ... etc ...
|}
Album
21
193
215
21
19
21
Matinée
21
19
Everything Is New
Love & War
{| class="wikitable sortable"
|-
! data-sort-type=text | Album
|-
 ... etc ...
|}
Album
21
193
215
21
19
21
Matinée
21
19
Everything Is New
Love & War

Without data-sort-type=text in the header, the tablesorter gets confused by the numeric titles in the first few rows into treating the entire column as numeric. This results in it wrongly sorting the non-numeric titles as zero regardless of the alphabetical ordering of their text.

Note that if a column without declared sort-type contains only numeric values, but with a reference <ref>...</ref> immediately after the last digit of at least one number, this may cause the column to be sorted as text (alphanumeric) by default, 1 12 2 27 289 3[17] 4 5 ... This can be avoided by declaring the sort type:! data-sort-type="number"|Elev. (ft) instead of ! Elev. (ft).

Default data type of a column

If you do not specify a data-sort-type, the sort modes (the data types, which, in addition to the choice "ascending" or "descending", determine the sorting order) are as follows:

date (see also below)
  • criterion: the first non-blank element is of the form "DD-MM-YYYY", "DD-MM-YY", or "DD mmm YYYY"
  • order: numeric value of YYYYMMDD; The string DDsMMsYYYY of length 10 (if characters positioned at s are equal together and are either '/' or '-' separator) is positioned as YYYYMMDD, the string DDsMMsYY of length 8 (if characters positioned at s are equal together and are either '/' or '-' separator) as 19YYMMDD if YY >= 50 and 20YYMMDD otherwise, and the string "DD mmm YYYY" with mmm an (abbreviated) month name.
isoDate (ISO 8601)
  • criterion: format "±YYYY-MM-DD", with 1-4 digits for year "YYYY" from -9999 to 9999, month only with digits, format "±YYYY-MM-DDThh:mm:ss.sss±TH:TM" with time hour "hh", minutes "mm", seconds "ss.sss", and time zone offset "TH:TM, right values are optional.
  • order: numeric, with time in milliseconds after 01 January, 1970 UTC.
currency (this mode can be useful for other data also)
  • criterion: the first non-blank element starts with $, £, €, or ¥
  • order: numeric, ignoring these symbols and all ordinary letters and commas, but not spaces; note that scientific notation cannot be used, as e and E are removed
number
  • criterion: the first non-blank element consists of just digits, points, commas, spaces, "+", "-", possibly followed by "e" or "E" and a string consisting of "+", "-", digits
  • order: after removing the commas and spaces, if any, if the string starts with a number the order is numeric according to the first number in the string (parseFloat is applied); it is regarded as zero if it is empty; in other cases (parseFloat returns NaN), the element is positioned like -∞.
Proposed internationalisation: In German etc., treat comma as a decimal point.
string
  • criterion: all other cases;
  • order: uses locale specific (so in this case English) ordering if your browser supports it. Alternatively after conversion of capitals to lowercase the order is ASCII – partial list showing the order: !"#$%&'()*+,-./09:;<=>?@[\]^_'az{|}~é— (see also below; a blank space comes before every other character; a non-breaking space code &nbsp; counts as a space; two adjacent ordinary blank spaces count as one; for multiple blank spaces one can use &nbsp; or alternate &nbsp; and ordinary blank spaces)

If more than one possible type matches, the first type in the above order is chosen. For example, "24-12-2007" matches as a date, so is not treated as a number. Formatting and markup tags are ignored when determining the matching type.

The sort mode is determined by the first 5 non-blank rows below the header after loading the page. This can also change after deleting a row, or adding a column. Therefore, it is wise to make sure that every element matches the criterion for the required data type. Using a row template this can be done very conveniently.

The method of making sure the sort mode of each column is as desired, is specify a data-sort-type, see up.

Specifying a sort key for a cell

Sometimes the value of a cell is not correctly parsed or one wants to sort the row in a special way. (e.g. a cell containing 'John Doe' should actually be sorted as 'Doe' and not as 'John'). This can be easily achieved by using {{sortname}}, like this: {{sortname|first|last|optional link target|optional sort key}}. Alternatively, you can set the data-sort-value attribute.

Wiki markup

{|class="wikitable sortable"
!Name and surname!!Height
|-
|data-sort-value="Smith, John"|John Smith||1.85
|-
|data-sort-value="Ray, Ian"|Ian Ray||1.89
|-
|data-sort-value="Bianchi, Zachary"|Zachary Bianchi||1.72
|-
!Average:||1.82
|}

This gives:

Name and surname Height
John Smith 1.85
Ian Ray 1.89
Zachary Bianchi 1.72
Average: 1.82

It is especially handy to sort military ranks in rank-seniority order.

Wiki markup

{|class="wikitable sortable"
!Name and surname!!Rank
|-
|data-sort-value="Smith, John"|John Smith||data-sort-value="16"|[[Corporal|Cpl]]
|-
|data-sort-value="Ray, Ian"|Ian Ray||data-sort-value="8"|[[Captain (OF-2)|Capt]]
|-
|data-sort-value="Bianchi, Zachary"|Zachary Bianchi||data-sort-value="10"|[[2nd Lieutenant|2 Lt]]
|}

This gives:

Name and surname Rank
John Smith Cpl
Ian Ray Capt
Zachary Bianchi 2 Lt

See also mw:Help:Sorting#Specifying a sort key.

If you have a list where all the entries start with quotes ("), and you want to set a sort key for one of the entries, then you will need to use the HTML name or number for quotes at the beginning of that sort key. See here too. Lists of song titles for example sometimes have each song title in quotes. So to sort by a particular word in a song title use one of these:

data-sort-value="&quot;WORD"

data-sort-value="&#34;WORD"

Keeping some rows together

data-sort-value can be used to keep certain rows together. The specified order of these rows is preserved. An example is to keep "South Holland" immediately after "Netherlands", whatever the sort order or column:

{|class="wikitable sortable"
!Country/province!!Capital
|-
|France||Paris
|-
|Netherlands||Amsterdam
|-
|data-sort-value=Netherlands|South Holland||data-sort-value=Amsterdam|The Hague
|-
|UK||London
|}
Country/province Capital
France Paris
Netherlands Amsterdam
South Holland The Hague
Poland Warsaw
UK London

If you have rows that contain colspans, this might become a little difficult. You can also use the class="expand-child" on a row; it will then always be below the row just above it in the table source, wherever that row may be sorted in the table.

{| class="wikitable sortable"
!style="width:9.3em"|Country!!Capital
|-
|'''France'''
|Paris
|- class="expand-child"
| colspan="2" | In Paris is the Eiffel Tower.
|-
|'''UK'''
|London
|- class="expand-child"
| colspan="2" | In the U.K. you cannot pay with euros,
|- class="expand-child"
| colspan="2" | and you drive on the left.
|-
|'''Germany'''
|Berlin
|- class="expand-child"
|colspan="2" | Germany includes the former DDR.
|}
Country Capital
France Paris
In Paris is the Eiffel Tower.
UK London
In the U.K. you cannot pay with euros,
and you drive on the left.
Germany Berlin
Germany includes the former DDR.

Examples of datatype auto detection. First 5 cells in a column

The script sees what the first 5 cells in a column contain. The sorting mode becomes numeric if the first 5 cells contain a number only (comma and period used in number formatting are accepted as number). The numeric sorting order is maintained even when text is found in the cells that follow the 5th cell. 123,564,589.7e12 is in scientific notation and is treated as a number. An empty cell is treated as a non-number when sorting numerically. There is an empty cell initially at the bottom of each of the 2 tables just below.

Datatype auto detection is inconsistent. It is always better to add a data-sort-type to the column header.

Sort order
auto-detected
as text
123,564,589.7e12
9
70
80 approx
-80
abc 80
aaa
600
300,000,000
3,000,000
Sort order
auto-detected
as number
123,564,589.7e12
9
70
-80
600
80 approx
abc 80
aaa
300,000,000
3,000,000

Single currency character, or single alphabetic character, does not currently change what the script determines via the first 5 cells in a column: Numerical order.

currencies
$ 9
$ 80
$ 70
$ 600
currencies
€ 9
€ 80
€ 70
€ 600
currencies
£ 9
£ 80
£ 70
£ 600
currencies
¥ 9
¥ 80
¥ 70
¥ 600
a
a 9
a 80
a 70
a 600
e
e 9
e 80
e 70
e 600

Percentage sign does not change from determination as numerical order. Number combinations with minus or divide signs within them are still detected as numbers. Their numerical ordering though is determined by the number before the minus or divide sign.

Percentage
7%
2
4
22
111
Number combinations
7-4
2
4
22/7
111

A plus sign in an empty cell among the first 5 cells breaks default numerical sorting. As does a plus sign after a number if it is in one of the first 5 cells in a column.

Sort order
auto-detected
as text
400
40,000
+
60,000
20,000
6,000
5,000
Sort order
auto-detected
as text
400
40,000
300+
60,000
20,000
6,000
5,000

Numerical sorting problems

Note: See the section above about datatype auto detection via the first 5 cells in a column.

Most of these problems can now be fixed by manually specifying the sort mode of a column by putting data-sort-type=number in the column header. See the example tables above and below. See also meta:Help:Sorting#Sort modes and the section about forcing the sort mode of a column.

To work data-sort-type=number needs to be in the header cell that contains the sorting icon. In tables with multi-row headers, the sorting icon will be in the lowest header cells.

References <ref>...</ref> after a number in any cell (including the first five cells) no longer break numerical sorting.

Text breaks default numerical sorting if it is before or after a number in one of the first 5 cells in a column.

A colon by itself (to signify no data, for example) in one of the first 5 cells in a column breaks numerical sorting.

Even when using data-sort-type=number in the column header, text in front of a number in any cell breaks numerical sorting of that cell. Text after a number is not a problem if the sort order of a column is specified by using data-sort-type=number.

"c." (circa, indicates "approximately") is often found in columns of numbers. It needs to be put after the number in order for numerical sorting to work. Alternatively, it can be moved to a different column. The addition of data-sort-type=number to the column header does not allow c. to be put in front of the number.

Leading zeroes are not necessary for numerical sorting of a column. If it seems that way, then that means the column is being sorted alphabetically. Look in the first 5 cells for anything other than numbers, and correct those cells according to these rules. Better yet, add data-sort-type=number to the column header. Later editing by other editors will not break numerical sorting.

A dash, of any kind, in a blank cell in one of the first 5 cells in a column breaks default numerical sorting of a column.

A dash in front of a number does not break numerical sorting.

Dashes are allowed anywhere in cells if data-sort-type=number is used in the column header.

The {{N/A}} template in the first five cells of a column is inconsistent in its effect on automatic datatype detection. It is always better to specify a data-sort-type in the column header.

Numerical ranges

Note: Most problems are fixed by adding data-sort-type=number to the column header. It also prevents problems caused by later editing.

A dash after a number no longer breaks default numerical sorting of a column. Therefore, a range (30–40) now works.

A plus sign after a number breaks default numerical sorting if it is in one of the first 5 cells in a column.

A plus sign in an otherwise empty cell breaks default numerical sorting of a column. That is if the cell is one of the first 5 cells in the column.

You can also use 2 columns for a range if you want to sort by either the lower or upper range. If you want the upper range to sort best all cells need to be filled in with numbers. For example; you can use the same number in both the lower and upper range. You can also add a plus sign after the number in the upper range.

The first set of tables below do not sort correctly, except for the lower range which has no complicating factors. Note that "400+" and "400 +" do not sort correctly in their columns. These tables do not have data-sort-type=number in their column headers.

One column
Estimated
attendance
400 +
40,000+
200,000–400,000
400,000+
60,000–350,000
40,000
40,000–50,000
20,000–100,000
10,000–100,000
6,000–7,000
5,000–10,000
One column
Estimated
attendance
400+
40,000+
200,000–400,000
400,000+
60,000–350,000
40,000
40,000–50,000
20,000–100,000
10,000–100,000
6,000–7,000
5,000–10,000
Two columns
Estimated
attendance
(lower) (upper)
400 400+
40,000 40,000+
200,000 400,000
400,000 +
60,000 350,000
40,000
40,000 50,000
20,000 100,000
10,000 100,000
6,000 7,000
5,000 10,000

data-sort-type=number has fixed the sorting in the tables below. Note the sorting of 400+ and 400 +.

One column
Estimated
attendance
400 +
40,000+
200,000–400,000
400,000+
60,000–350,000
40,000
40,000–50,000
20,000–100,000
10,000–100,000
6,000–7,000
5,000–10,000
One column
Estimated
attendance
400+
40,000+
200,000–400,000
400,000+
60,000–350,000
40,000
40,000–50,000
20,000–100,000
10,000–100,000
6,000–7,000
5,000–10,000
Two columns
Estimated
attendance
(lower) (upper)
400 400+
40,000 40,000+
200,000 400,000
400,000 +
60,000 350,000
40,000
40,000 50,000
20,000 100,000
10,000 100,000
6,000 7,000
5,000 10,000

Date sorting problems

Month names

All sorting involving month names may fail for registered users who have changed the default language setting "en - English" at Special:Preferences. It affects relatively few users on the English Wikipedia and can be ignored.

Year only

Year sorting of a column works as long as the year is the first text in each cell in the column. Adding data-sort-type=date to the column header does not change this.

Text is OK after a year in a cell. "FY" (fiscal year), for example, should go after the year. References after the year are OK. Put "c." after the year, or use "est." after the year instead.

A dash, of any kind, in a blank cell breaks year sorting of a column. Dashes after the year are OK.

Unlike for numerical sorting the {{N/A}} template in any cell in a year column does not break year sorting of that column.

If there are problems with year sorting check for any cells in the column with text or a dash (of any kind) as the first thing in a cell. Remove that text or dash, and the column should sort correctly.

Year and month

Date sorting does not work for columns with only the year before the month (no day). The {{Date table sorting}} template will work.

Adding data-sort-type=date or data-sort-type=isoDate to the column header does not help. Click each column header a couple times in the tables below to see. Note the column headed data-sort-type=isoDate may sort correctly in some browsers, but it is not reliable.
Year and month in numerical form (YYYY-MM) works with data-sort-type=isoDate (see relevant section farther down).

Year and month
1999 Dec
1999 Jan
2004 May
2004 Aug
Year and month
1999 December
1999 January
2004 May
2004 August
Year and month
data-sort-type=date
1999 Dec
1999 Jan
2004 May
2004 Aug
Year and month
data-sort-type=date
1999 December
1999 January
2004 May
2004 August
Year and month
data-sort-type=isoDate
1999 December
1999 January
2004 May
2004 August

Month and year

Date sorting does not work for columns with only the month before the year (no day). Adding data-sort-type=date to the column header does not help. The {{Date table sorting}} template will work.

Month and year
Dec 1999
Jan 1999
May 2004
Aug 2004
Month and year
December 1999
January 1999
May 2004
August 2004
Month and year
data-sort-type=date
Dec 1999
Jan 1999
May 2004
Aug 2004
Month and year
data-sort-type=date
December 1999
January 1999
May 2004
August 2004

Month, day, and year

Sorting works correctly in all the tables below. Years before 100 (for example, year 99) break sorting. If a number for a day is missing, sorting is broken.

Month, day, year
Dec 5, 1999
Jan 7, 1999
May 14, 2004
Aug 4, 2004
Month, day, year
December 5, 1999
January 7, 1999
May 14, 2004
August 4, 2004
Month, day, year
data-sort-type=date
Dec 5, 1999
Jan 7, 1999
May 14, 2004
Aug 4, 2004
Month, day, year
data-sort-type=date
December 5, 1999
January 7, 1999
May 14, 2004
August 4, 2004

Day, month, and year

Sorting works correctly in all cases below. Years before 100 (for example, year 99) break sorting. If a number for a day is missing, sorting is broken.

Day, month, year
5 Dec 1999
7 Jan 1999
14 May 2004
4 Aug 2004
Day, month, year
5 December 1999
7 January 1999
14 May 2004
4 August 2004
Day, month, year
data-sort-type=date
5 Dec 1999
7 Jan 1999
14 May 2004
4 Aug 2004
Day, month, year
data-sort-type=date
5 December 1999
7 January 1999
14 May 2004
4 August 2004

Before year 100. Any date format

Note: See related section that follows this one.

Sorting can be done via the hidden data-sort-value using the ISO date. Combined with data-sort-type=isoDate

Note: The sorting wikitext is the same for the 2 tables below. Any date format can be shown to the readers.

Date
5 Dec 111
7 Jan 35
5 Dec 207
111 BC
7 Jan 35 BC
Dec 207 BC
{| class="wikitable sortable" 
|-
! data-sort-type=isoDate | Date
|-
| data-sort-value="111-12-05" | 5 Dec 111
|-
| data-sort-value="35-01-07" | 7 Jan 35
|-
| data-sort-value="207-12-05" | 5 Dec 207
|-
| data-sort-value="-111" | 111 BC
|-
| data-sort-value="-35-01-07" | 7 Jan 35 BC
|-
| data-sort-value="-207-12" | Dec 207 BC
|}

The table below uses the same isoDate values for data-sort-value as the above table. But multiple formats are used for showing the dates to the reader.

Date
5 Dec 111
7 January 35
Dec 5, 207
about 111 BC
Jan 7, 35 BC
December 207 BC
{| class="wikitable sortable"
|-
! data-sort-type=isoDate | Date
|-
| data-sort-value="111-12-05" | 5 Dec 111
|-
| data-sort-value="35-01-07" | 7 January 35
|-
| data-sort-value="207-12-05" | Dec 5, 207
|-
| data-sort-value="-111" | about 111 BC
|-
| data-sort-value="-35-01-07" | Jan 7, 35 BC
|-
| data-sort-value="-207-12" | December 207 BC
|}

Year, month, day. Using numbers. ISO date YYYY-MM-DD

For examples; see tables in Android One. It uses YYYY-MM without the day.

See: ISO date. "±YYYY-MM-DD", with 1 to 4 digits for year "YYYY" from -9999 to 9999. Year by itself is fine. If month is used, then it must use two digits. Sorting is broken if the month uses a single digit without the preceding zero. One digit is fine for the day. Some stuff before and after the date is allowed. Such as "c." (for circa) before the date, and references after the date. Remember to leave a space in the wikitext before years that are a negative number. Otherwise, |- will be used as table formatting.

Sorting works best with data-sort-type=isoDate added to the column header. BC or BCE can be added then too. Test other additions before and after the date. Single digits for months can still be a problem in some tables.

Date
(year-month-day)
Wrong. M versus MM.
Sorting is broken
-90
c. 90
90-1-13
90-12-5
1011-08-01[2]
c. 207-11[1]
Date
(year-month-day)
isoDate is correct.
Sorting works
-90
c. 90
90-01-13
90-12-5
1011-08-1[2]
c. 207-11[1]
Date
(year-month-day)
isoDate is correct,
but BCE breaks sorting
-90 BCE
c. 90
90-01-13
90-12-5
1011-08-1[2]
c. 207-11[1]
Date
(year-month-day)
works better with
data-sort-type=isoDate
Sorting works with BCE
-90 BCE
c. 90
90-01-13
90-12-5
1011-08-1[2]
c. 207-11[1]

Numeric sort for BC/AD years

In certain circumstances the following sort technique may be used to provide a simple intuitive numeric sort for BC/AD years which are often surrounded by qualifying text. The sort in the BC/AD column (the fourth column in the example) is forced to be numeric (just as the other columns are forced to be alphabetic) by the inclusion of the first two hidden rows. These rows contain extreme values which will mean that these rows will ALWAYS be sorted to the top and bottom of the table, regardless of which column is sorted. As the sort mechanism determines the sort type to be used by examining the first cell in the column to be sorted the hidden rows ensure that a purely numeric value is always found in the first or last cell.

The mechanisms used here are explained in the current article at: #Specifying a sort key for a cell and #Examples of datatype auto detection. First 5 cells in a column.

With numeric sorting guaranteed all that is needed is to precede the BC/AD text with a positive or negative year number in a hidden sort key ( {{Hs|-9999 ! }} ) which suitably represents the cell text. As this number will be the first thing the sort code sees it will sort it as a number, in the order large negatives -> zero -> large positives, or the opposite. Once the sort type is fixed at the start of a sort the presence of alphabetic values in subsequent rows is ignored. The sort is done numerically on the first text in each row. The detail of the exclamation mark after the positive/negative year number in the hidden sort key is to clearly mark an end to the number which the sort mechanism must consider. In certain circumstance, if the exclamation mark is not present, and the hidden sort key is immediately followed by another number, that number may be treated as a continuation of the hidden sort key number, to produce an incorrect sort.

The tables in the article: List of cities by time of continuous habitation have been modified to used this sort. The following example is modified (maintaining some defects in the content which will need resolving in the original!) from one of those tables:

{| class="wikitable sortable"
|-
! Name
! Historical region
! Location
! Continuously inhabited since
! class="unsortable" | Notes

<!-- force numeric sorting on the hidden values in col 4 with hidden extreme max and min rows -->
|-style="display:none;"
|!a||!a||!a||-9e99

|-style="display:none;"
|~z||~z||~z||9e99

|-
| [[Ife]] || || {{Hs|Nig}}[[Osun State]], [[Nigeria]] || {{Hs|-500 !}}c. 500 BC ||

|-
| [[Axum]] || [[Kingdom of Axum]] || [[Ethiopia]] || {{Hs|-400 !}}c. 400 BC || Ancient capital of the Kingdom of Axum
.
.
.
|-
| [[Mogadishu]] || || [[Somalia]] || {{Hs|900 !}}c. 900 || settled by Arab traders

|-
| [[Dar es Salaam]] || || [[Tanzania]] || {{Hs|1865 !}}1865 || Founded by the Sultan of [[Zanzibar]].

|}
Name Historical region Location Continuously inhabited since Notes
!a !a !a -9e99
~z ~z ~z 9e99
Ife Osun State, Nigeria c. 500 BC
Ife Osun State, Nigeria c. 8th century earliest traces of habitation date to the 4th century BC.
Yeha D'mt Ethiopia c. 700 BC Oldest site of continuous habitation in Sub-Saharan Africa.
Axum Kingdom of Axum Ethiopia c. 400 BC Ancient capital of the Kingdom of Axum
Igodomigodo Kingdom of Benin Nigeria c. 400 BC City of Benin, one of the oldest cities in Nigeria
Djenné-Jeno Mali c. 200 BC oldest known city in sub-Saharan Africa
Mogadishu Somalia c. 900 settled by Arab traders
Cape Town Cape Colony South Africa 1652 Founded by Jan van Riebeeck of the Dutch East India Company
Monrovia Liberia 1822 Settled by freed American slaves through the American Colonization Society
Dar es Salaam Tanzania 1865 Founded by the Sultan of Zanzibar.

Date table sorting template: Day and month, Many other date formats

The simplest way to format sortable dates in a table is to use the {{Date table sorting}} template. In accordance with the manual of style, the template would be invoked using the following format:

  • {{Date table sorting|4 July 1776}}

These example tables use the {{Date table sorting}} template. They all sort correctly. See the wikitext ("edit source"). The wikitext for the first entry in each table is shown in the table header.

Note: None of the table columns use the data-sort-type= modifier. Using data-sort-type= can sometimes break sorting.

The {{Date table sorting}} template can be used with many date formats. See Template:Date table sorting for many more options.

Date
(Day and month only)
{{Date table sorting|4 Jan}}
4 Jan
28 Aug
3 Jan
29 Aug
14 Dec
1 Jan
Date
(Month and day only)
{{Date table sorting|January 4}}
January 4
August 28
January 3
August 29
December 14
January 1
Date
(Month, day, year)
{{Date table sorting|1990|4|27}}
April 27, 1990
August 8, 1989
February 3, 2006
October 4, 2006
November 1, 2004
January 11, 2004

Issues

String sort mode
2006-12-032006-12-03
-0000-03-27-0000-03-27
2006-12 December 2006
!9936-04 April 64 BC
!9900-07-13-0099-07-13
!9937-09-23-0062-09-23
!9937-10-08-0062-10-08
!9998-12-21-0001-12-21
2006-11-082006-11-08
0304-12-310304-12-31
2005-05-152005-05-15

Date sorting works by formatting dates so they can be sorted numerically. For example:

  • yyyy mm dd

or

  • 2001 07 21

...for 21 July 2001. The "display:none" style can be used to hide a sortable numeric date before the displayed date. {{Date table sorting}} does this automatically, and is recommended in most cases.

You can use July 7, 2012 etc. to get sortable dates. Example, including one date with a different display format:

Date
January 7, 2012
May 7, 2012
4 July 2012
July 7, 2012

For years BC, !9937-09-23 can be used for -0062-09-23 (62 BC): Simply subtract the year BC from 10,000.

Background colors in sortable headers

Adding color with generic "background:...;" property in a header may cause that column to lose its sorting button – see phab:T33755. Example:

Name Surname Height
John Smith 1.85
Ron Ray 1.89
Mario Bianchi 1.72
Average: 1.82

Use the more specific style="background-color:...;" to make things work correctly. Example:

{|class="wikitable sortable"
|-
!style="background-color:navajowhite" | Name
!style="background-color:navajowhite" | [[Surname]]
!style="background-color:navajowhite" | [http://example.com Height]
|-
|John
|Smith
|1.85
|-
|Ron
|Ray
|1.89
|-
|Mario
|Bianchi
|1.72
|- class="sortbottom"
|colspan="2" |Average: 
|1.82
|}

Produces this sortable table:

Name Surname Height
John Smith 1.85
Ron Ray 1.89
Mario Bianchi 1.72
Average: 1.82

Tips and tricks

Padding

Sometimes entries are padded on the left for alignment purposes. This can adversely affect how they are sorted.

Non-breaking spaces

The effect of left-padding with non-breaking space codes &nbsp; which render as blank spaces, depends on the browser: in IE they are (unlike actual blank spaces) counted for sorting as leading blank spaces, so in a list of numbers with text (for which the alphabetic sorting mode applies) they could be used to equalize the number of characters before the explicit or implicit decimal separator. However, in Firefox they are ignored for the purpose of sorting.

Sorting using &nbsp; works on IE but not on Firefox Name
100.3 FM Third
 89.5 FM First
107.3 FM Fourth
 95.3 FM Second

See also Talk:List of U.S. states and territories by population/Archive 1#Sortable Table.

Padding with zeros

Example:

  • 000156

Formatnum can be combined with padleft:

Integer:

{{formatnum:{{padleft:299792458|16|0}}}} gives:

  • 0,000,000,299,792,458

Real:

{{formatnum:{{padleft:{{#expr:((299792458.056 - .5) round 0)}}|16|0}}}}.{{padleft:{{#expr:(1000000*(299792458.056 - ((299792458.056 - .5) round 0))) round 0}}|6|0}} gives:

  • 0,000,000,299,792,458.056000

Controlling sorting and display

Text undesired for sorting but needed for display:

  • In numeric sorting mode, text breaks numerical sorting whether the text is before or after the number. Sorting then becomes alphanumeric. Empty cell is treated as "zero" when sorting numerically.
  • In date sorting mode, this text needs to be put in a separate column; in the case of a cell containing a range of dates or numbers (e.g. from .. to ..), text in surplus of what is required for sorting is put in the extra column. If the first part of the text is used for sorting, then the extra column needs to be the following one; conversely, if the last part of the text is used for sorting, then the extra column needs to be the previous one; depending on the table format, this dividing of an item over two cells may look ugly.
  • In alphabetic sorting, any footnotes etc. do not require a separate column; they can simply be put at the end of the element.

Text undesired for display but needed for sorting:

  • can be put as hidden text in the column to be sorted

Combining the two, we can have displayed text independent of text used for sorting, by fully hiding the latter, and fully putting the former in a separate column (in date sorting mode and numeric sorting mode) or in the same column after the hidden text (in alphabetic sorting). Fully putting the displayed text in a separate column may look ugly if it is not done consistently for a whole column, but only for elements that require this (e.g. if most entries in a column are single numbers, but some are ranges).

Sorting with increase/decrease/steady templates

Example Without key With key
Apple Increase10 1010
Banana Increase2 22
Cherry Decrease1 -11
Durian Steady 0

To enable sorting of cells with Template:Increase, Template:Decrease or Template:Steady, add a sort key, e.g. {{increase|2}}2, {{decrease|-1}}1 or {{steady|0}}. To fix an existing table, use Search and replace (right icon in the Advanced toolbar) with Treat search string as a regular expression selected to do the following replacements:

Search for Replace with
(\{\{increase)(\}\})([0-9]*) $1|$3$2$3
(\{\{decrease)(\}\})([0-9]*) $1|-$3$2$3
(\{\{steady)(\}\}) $1|0$2

Maintaining tables sorted alphabetically or by rank

It used to be difficult to maintain tables in rank order, and to keep the numbering correct. That is no longer true. Template:Static row numbers renumbers the row numbers after every change in row order. And after every addition or deletion of rows.

Putting a table in initial alphabetical order

Note: For more info see the Visual Editor sections at the end of Help:Table. See also: Phab:T240114: "Enhance the VisualEditor table editor to allow sorting and saving sorted content."

There are fast and slow ways to do this. Scroll down for fast ways. Unfortunately, the Visual Editor does not have a fast way to quickly alphabetize a table. You can manually move rows around one at a time. Click on a cell in a row. Then click on the arrow that shows up at the left of the row. Then click on "move above" or "move below" as needed.

If this is buggy or is not working, the rows can be moved around in the wikitext by cutting and pasting rows in the wikitext.

A fast way is to launch free LibreOffice Calc, or another spreadsheet program. To see how go to Help:Table#Sort alphabetically or numerically with free spreadsheet and VE. For more info see Commons:Convert tables and charts to wiki code or image files.


There is another way to alphabetize a table. One can use NoteTab Light (freeware version of NoteTab). It installs quickly and easily. But the wikitext must be in compressed table format. All the wikitext for a row must be on one line. That means the cells in that row are separated by double bars ||.

To alphabetize the list by the first column paste the table wikitext into a new NoteTab Light page. Select the rows you want to alphabetize. Then click on the "modify" menu, then "lines", then "sort", and then "ascending". That will put "A" at the top and "Z" at the bottom.

Then put back |- (wikitext for row) between each line. Do that via find-and-replace by replacing ^p with ^p|-^p

^p is the underlying text editor code for line breaks in NoteTab.
|- is the wikitext for a table row.

If there are blank lines between the entries replace ^p^p with ^p|-^p

Copy the wikitext and paste it back into the article. Save the page.

Initial alphabetical sort versus initial sort by rank order

It is a good idea to keep lists and tables in some kind of initial non-random sort order. It no longer matters what method you choose. Template:Static row numbers will maintain row numbering automatically no matter what changes you make to the row order.

Removing an old rank column (1,2,3) from a table

You can remove the rank column cells quickly. It is much easier now with the table editor in the VisualEditor. Click on the header in the column you want to delete. An arrow will show up at the top of the column. Click the arrow, and then "delete column".

Then let Template:Static row numbers create the row number column.

Auto-ranking or adding a row numbering column (1,2,3)

There are Phabricator threads asking for a way to easily add static row numbers to tables. See phab:T42618. It supersedes phab:T42634.

In the meantime there is Template:Static row numbers. It is easy to use now. It is a template to automatically add row numbers to sortable tables. The row numbers will not be sorted when columns of data are sorted. A possible note to add above a table: Row numbers are static. Other columns are sortable. This allows ranking of any column.

These templates work together with it, and make table formatting fast:

For example; they are used together here: List of U.S. states and territories by incarceration and correctional supervision rate.

Note that style=max-width:Xem is selectively used to narrow columns with wordy header text without using breaks <br>. Header breaks annoy people using screen readers due to the pauses.

The selective use of max-width allows the state names to spread out, and stay on one row each if the screen is wide enough. This allows easier scanning down or across the rows. Yet when the screen becomes narrower and narrower, the state names will eventually wrap. This is good for cell phones. Use em unit settings instead of px. Em units expand in width as the font size is increased.

Be sure to check both mobile and desktop views (links at bottom of page). Remember that mobile view does not have sorting capability. Check to see that header rows aren't being given a row number. Also check that the max-width settings aren't too tight. Mobile view may need a slightly larger max-width setting for some columns. And different desktop browsers, and different settings for them, can make some max-width settings cause some column header text to overlap into the adjacent column. So it is usually good to add some extra em units to the max-width settings.

Alphabetic sorting order

data-sort-type:text - Sort the following table to see an example of the alphabetic sort order.

Note that sorting is case-insensitive: the two-character entries such as A1 demonstrate that A and a are at the same position.

Test
!
"
#
$
%
&
'
(
)
*
+
,
-
.
/
0
9
:
;
<
=
>
?
@
[
\
]
^
_
'
A
Z
a
z
A1
Z1
a1
z1
{
|
}
~
É
é
É1
é1

Numerical sorting order examples

data-sort-type:number - Sort the following table to see an example of the numerical sort order.

mixed notations
Test
1.4285714285714E+17
1000000000000000000
-1000000000000000000
.0000000000000000001
-.0000000000000000001
-1.4285714285714E+17
1.4285714285714E-13
-1.4285714285714E-13
89 123 456 788
89,123,456,789
14
-14
11
-12 (retrograde)
12 or 13
12 (?)
c. 12
12 (approx.)
?
333
1e10
e 9
e 80
e 70
e 600
999e9
88e80
7e270
999e-9
88e-80
7e-270
-999e9
−999e9
-88e80
-7e270
-999e-9
-88e-80
-7e-270
e3
-e3
1e3
e9
e80
e270
6e11
8e11

See also