I'm new to MySQL and have written the following query (see below). **PROBLEM:** The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes 0.06sec. I have indexes on each of the ORDER BY columns and am unsure what else to do. If anybody can rewrite/recommend changes it'd be greatly informative. **CODE:** SELECT DISTINCT username FROM users WHERE in_progress = 0 AND scraped_from IN (SELECT DISTINCT username FROM source_accounts WHERE group_users = 'New Users' AND (type = 'users' OR type = 'both') AND use = '1') AND username NOT IN (SELECT user_tofollow FROM follow_history WHERE owner_account = 'admin') ORDER BY real_user DESC, IF((last_used) IS NULL,'0','1'), IF((last_update)>3,'1','0'), DATE(last_used), IF((user_ratio)>100,'1','0') LIMIT 1000; **EXPLAIN:** 1 PRIMARY scraped_users ref idx1,scraped_from,in_progress,username in_progress 1 const 687025 Using where; Using temporary; Using filesort 1 PRIMARY eq_ref 767 userstack.users.scraped_from 1 Distinct 2 MATERIALIZED source_accounts ref username,group_users,type,use group_users 767 const 48 Using index condition; Using where; Distinct 3 DEPENDENT SUBQUERY follow_history index_subquery user_tofollow user_tofollow 767 func 1 Using where

Descending sort type does not work with this syntax, please help. prepare("SELECT * FROM informations ORDER BY gender ASC, first_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "last_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "first_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "timestamp") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp ASC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }else{ } }elseif(isset($_GET["sorttype"]) == "desc"){ if($compare == "gender"){ $holdData = $conn->prepare("SELECT * FROM informations ORDER BY gender DESC, first_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "last_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY last_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "first_name") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY first_name DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }elseif ($compare == "timestamp") { $holdData = $conn->prepare("SELECT * FROM informations ORDER BY timestamp DESC"); $holdData->execute(); $getData = $holdData->fetchAll(PDO::FETCH_ASSOC); $newData = json_encode($getData);; print_r($newData); }else{ } }else{ } }else{ echo json_encode(["Message"=>"No Sortfield and Sort type found!"]); echo "
"; echo json_encode(["Message"=>"Please select the sortfield and sort type!"]); }

I am using SQL Server 2022 Developer Trying get all AccessLog that classified to type 1.

SELECT [t].[Time], [u].[UserName], [t].[Type], [t].[Message]
FROM [AccessLog] AS [t]
         LEFT JOIN [AppUser] AS [u] ON [t].[UserId] = [u].[Id]
WHERE EXISTS (SELECT 1
              FROM [LogCatalog] AS [c]
              WHERE [c].[Type] = 1
                AND [c].[Name] = [t].[Type])
ORDER BY [t].[Time] DESC

For 1M record, it will need ~90s to execute on my computer. Most cost is on sort operate. I already have index on AccessLog.Time DESC, but the plan will sort again still after join. https://www.brentozar.com/pastetheplan/?id=HyXzc9UUp I have Index on AccessLog: 1. PK [Id] 2. IX [Time] DESC 3. IX [Time] DESC, [Type] ASC 4. IX [Type] ASC, [Time] DESC 5. IX [Type] ASC 6. IX [UserId] ASC 7. IX [Time] DESC, [UserId] ASC, [Type] ASC The query filter by [Type] and order by [Time], why the plan can not use the [Time],[Type] index but need to sort again?

Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select? (assuming unique alphanumeric index and no auto-increments if these matter)

I have an environmental logger and I am trying to select every 5th row starting at where outsidetemp is not null (I want to include the null values after the first not null value). Is there a way to offset to find the first not null value? My line of code is below everything is working except I cannot get the offset set.

SELECT * FROM (SELECT @row := @row+1 AS rownum, ttime,temperature,humidity,outsidetemp
FROM (SELECT @row :=0) r, tempHumiditydata) ranked
WHERE rownum % 5 = 1
ORDER BY rownum DESC LIMIT 2020 ??????OFFSET (WHERE outsidetemp IS NOT NULL)????????

Additional background. The outsidetemp is being logged once for every 60 of the other data points the values in between these points is Null. My code above is being used to graph the data so it is important I start at the first not null value to capture the remaining not null values (starting at the wrong point and I could not get any data for outsidetemp). I am skipping every 5th row to reduce the amount of data in a wider time frame graph. Here is an example of what I need Ttime temp humidity outsidetemp 8:00 71 50 NULL 7:00 72 49 80 6:00 73 48 NULL 5:00 73 48 NULL 4:00 73 48 NULL 3:00 69 51 76 Using only every other record starting at the first non null outsidetemp yields 7:00 72 49 80 5:00 73 48 NULL 3:00 69 51 76 I can then use the above to graph temp, humidity and outside temp versus time. I just interpolate between non null outside temp values.

Only difference in the below query is the order by column. The resultset of both query is same. However, the 2nd query execution plan shows 'Using temporary; Using filesort' for ordering where as for 1st query it does not require filesort. Apart from id I have index on c.table_b_id and c.some_indexed_id SELECT * FROM TableA a inner join TableB b on a.id = b.id inner join TableC c on c.table_b_id = b.id where c.some_indexed_id = 1 order by c.table_b_id asc; SELECT * FROM TableA a inner join TableB b on a.id = b.id inner join TableC c on c.table_b_id = b.id where c.indexed_id = 1 order by b.id asc;

Varchar type column "CodeName", that contain number, prefix or suffix with number it depends on. I want it to be sorted specific way. Below i have defined table with data, CREATE TABLE TempTable ( CodeName VARCHAR(45) NULL ) INSERT INTO TempTable VALUES ('AK-2A'), (NULL), ('PT-4'), ('5'), ('6A'), ('PT-13'), (' '), ('AK-04'), ('FWS-5'), ('6B'), ('AK-1'), ('FWS-03'), ('13'), ('AK-2B'), ('12'), ('FWS-10'), (NULL), ('FWS-17'), ('PT-3B'), ('FWS-117A'), ('PT-01'), ('PT-3A'), ('PT-02'), (''), ('PT-17'), ('PT-06'), ('PT-03'), ('AK-03'); Above insert query including Null and empty value as well. I want to sort it order like ascending first number after character alphabet wise order ex: AK-1, BC-01, PT-01, PT-2, PT-3A, PT-3B, ZX-01, ZX-05, ZX-Z6 etc. For example, this would be an acceptable sort order like this, **Note:** some times if Null or Empty string inserted without Conversion failed, what is the best way to achieve the sort order i have mentioned on example (image) Thank You.

I have spent a long time looking for this, and I am getting mixed messages. In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause:

SELECT *
FROM orderby
ORDER BY string COLLATE … ;

--	SQLite:		BINARY | NOCASE
--	MariaDB:	utf8mb4_bin | utf8mb4_general_ci
--	Oracle:		BINARY | BINARY_CI
--	MSSQL:		Latin1_General_BIN | Latin1_General_CI_AS

I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL. Is there a COLLATE clause value that would sort Case Insensitive? I know there are many questions regarding case sensitivity, but (a) most of them are old and (b) none that I have seen relate to the COLLATE clause. FWIW, I am testing on PostgreSQL 11.8. I have a test fiddle on http://sqlfiddle.com/#!17/05cab/1 , but it’s only for PostgreSQL 9.6. MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

i would like to order my query output the way Windows Explorer does. First special characters like _, then the numbers, and the letters last. _ttt.bmp 0.gif bbb.png

i am stuck with an exercise. using WideWorldImporters DataBase I need to show my results this way: but no matter what I do in order to get to this exact solution I can't, because the month column is alphanumeric and not numeric. the closest I got is this code I would like some insights in order to solve this problem. thanks all!! WITH T1 AS ( SELECT YEAR(o.OrderDate) AS OrderYear ,MONTH(o.OrderDate) AS OrderMonth ,SUM(ol.PickedQuantity*ol.UnitPrice) AS MonthlyTotal --,SUM(SUM(ol.PickedQuantity*ol.UnitPrice))OVER(PARTITION BY YEAR(o.OrderDate) ORDER BY MONTH(o.OrderDate)) AS CumulativeTotal FROM Sales.OrderLines ol JOIN Sales.Orders o ON o.OrderID = ol.OrderID GROUP BY YEAR(o.OrderDate),YEAR(o.OrderDate),MONTH(o.OrderDate) ) SELECT OrderYear ,CAST(OrderMonth AS VARCHAR) AS OrderMonth ,FORMAT(MonthlyTotal,'#,#.00') AS MonthlyTotal ,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS CumulativeTotal FROM t1 UNION ALL SELECT t1.OrderYear ,'Grand Total' AS OrderMonth ,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS MonthlyTotal ,FORMAT(SUM(MonthlyTotal)OVER(PARTITION BY OrderYear ORDER BY OrderMonth), '#,#.00') AS CumulativeTotal FROM t1 WHERE (t1.OrderYear = 2013 and OrderMonth = 12) OR (t1.OrderYear = 2014 and OrderMonth = 12) OR (t1.OrderYear = 2015 and OrderMonth = 12) OR (t1.OrderYear = 2016 and OrderMonth = 5) ORDER BY OrderYear

I want to find a user's favorite music list. First step, I query all id from user's favorite table. In the next step I query the music list by id from songs table. But now I want to sort rows by the timestamp column favorite.added_at desc. I do not want to join table, so I want to figure out if it's possible to sort by the input id in the in query. For example: -- the 3087 is first record select * from songs s where id in (3087,5122) -- the 5122 is first record select * from songs s where id in (5122,3087) I have tried in PostgreSQL 13, and the row with id = 3087 was always first. Seems like PostgreSQL does not fetch rows by the sequence of values in the IN list?

Suppose I want the order of output records match the order in the IN clause

SELECT * FROM (
SELECT 1 AS id, 'one' AS text FROM DUAL
UNION ALL
SELECT 2 AS id, 'two' AS text FROM DUAL
) t WHERE t.id IN (2,1)

Expected: |id|text| |-|-| |2|two| |1|one| Actual: |id|text| |-|-| |1|one| |2|two| Obviously, I can't hardcode it like this (GPT's suggestion). Ids and their order are dynamic

SELECT * FROM (
  SELECT 1 AS id, 'one' AS text FROM DUAL
  UNION ALL
  SELECT 2 AS id, 'two' AS text FROM DUAL
) t 
WHERE t.id IN (2, 1)
ORDER BY CASE t.id
  WHEN 2 THEN 1
  WHEN 1 THEN 2
END;

How do I do it? Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production (bonus points if it works for PostgreSQL as well)

I have these 2 tables CREATE TABLE users ( id bigint NOT NULL AUTO_INCREMENT, status varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=330031656 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; CREATE TABLE user_meta ( id int NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, meta_id bigint NOT NULL, value bigint NOT NULL, PRIMARY KEY (id), KEY usermeta_user_id_meta_type_meta_value (user_id,meta_id,value), CONSTRAINT user_meta_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ) ENGINE=InnoDB AUTO_INCREMENT=16728 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; The customer requirement is that they want to sort based on value, for a specific meta_id. For example, let's say there are 10 meta_ids, and the customer selects meta_id=1111. In this case, all the users who have meta_id=1111 associated should be sorted by their value first, and the other users who don't have meta_id=1111 can come in any order at the bottom. There have been similar questions asked and I've tried to form queries based on their answers, but they don't seem to work for me. This [answer](https://stackoverflow.com/a/3550974) and [this one too](https://dba.stackexchange.com/questions/201719/how-to-use-a-conditional-order-by-in-mysql) talk about using if-else and case-when-then statements, but when I try any of these

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by if(um.meta_id=1111, value, 1);

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case um.meta_id
when 1111 then value else 1 end;

select u.id, um.meta_id, um.value from users u
inner join user_meta um on um.user_id = u.id
order by case 
when um.meta_id = 1111 then value else u.id end;

I get this

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 326480529 |     200 | 1730358000 |
| 326850494 |    1111 | 1730185200 |
| 326785127 |    1111 | 1730271600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 328079379 |    1111 | 1730793600 |
+-----------+---------+------------+

I want all the users with meta_id=1111 to come at the top, but neither do they come at the top, nor are they sorted within themselves. Similarly, for desc order, the ones with meta_id=1111 should come at the top, sorted in descending order, and all the others can come at the bottom, such as this

+-----------+---------+------------+
| id        | meta_id | value      |
+-----------+---------+------------+
| 328079379 |    1111 | 1730793600 |
| 326833934 |    1111 | 1730358000 |
| 326467136 |    1111 | 1730358000 |
| 326785127 |    1111 | 1730271600 |
| 326850494 |    1111 | 1730185200 |
| 326480529 |     200 | 1730358000 |
+-----------+---------+------------+

I'd really appreciate any help or direction in getting this right. Thank you so much! I'm also posting some INSERT statements for both the tables to make it easier to replicate on your local machines

INSERT INTO users (id,status) VALUES (328079379,'active');
INSERT INTO users (id,status) VALUES (326833934,'active');
INSERT INTO users (id,status) VALUES (326467136,'deleted');
INSERT INTO users (id,status) VALUES (326785127,'inactive');
INSERT INTO users (id,status) VALUES (326850494,'removed');
INSERT INTO users (id,status) VALUES (326480529,'active');


INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13155,328079379,1111,1730793600);
INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13045,326833934,1111,1730358000);
INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13009,326467136,1111,1730358000);
INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13010,326785127,1111,1730271600);
INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13051,326850494,1111,1730185200);
INSERT INTO user_meta (id,user_id,meta_id,value) VALUES (13008,326480529,200,1730358000);

The following is illegal.

SELECT
  schema_id + 2 AS evil_column
FROM
  sys.tables
ORDER BY
  evil_column + 3;

If you remove the + 3, then it is legal. Assuming that I want to do arithmetic with evil_column and ORDER BY the result but do not want to repeat the definition of evil_column anywhere, what are my options?

I'm working on optimizing a query in PostgreSQL, and I've encountered a performance issue when using the ORDER BY clause. The query is intended to search profiles based on a similarity match to a name (for example: 'john') and then order the results by a computed score. The score is a combination of word similarity and whether the profile has an avatar. Here's the query: SELECT uuid, type, byline, display_name, username, avatar, ( word_similarity('john', search_text) + CASE WHEN avatar != '' THEN 1 ELSE 0 END ) AS combined_score FROM test_mv_all_profiles WHERE 'john' Sort (cost=35130.07..35158.41 rows=11335 width=52) (actual time=8092.502..8092.565 rows=100 loops=1) Sort Key: ((word_similarity('john'::text, search_text) + (CASE WHEN ((avatar)::text ''::text) THEN 1 ELSE 0 END)::double precision)) DESC Sort Method: top-N heapsort Memory: 51kB Buffers: shared hit=66811 -> Bitmap Heap Scan on test_mv_all_profiles (cost=187.84..34696.86 rows=11335 width=52) (actual time=69.060..8052.737 rows=90765 loops=1) Recheck Cond: ('john'::text Bitmap Index Scan on test_idx_mv_social_profile_search_text_trigram_idx_gin (cost=0.00..185.01 rows=11335 width=0) (actual time=58.323..58.323 rows=91483 loops=1) Index Cond: ('john'::text Index Scan using test_idx_mv_social_profile_search_text_trigram_idx on test_mv_all_profiles (cost=0.42..44444.13 rows=11335 width=52) (actual time=0.506..4.417 rows=100 loops=1) Index Cond: ('john'::text <% search_text) Rows Removed by Index Recheck: 1 Buffers: shared hit=311 Planning time: 0.118 ms Execution time: 4.482 ms My questions: - Why does the ORDER BY clause slow down the query so much? - Is there a way to optimize this query while keeping the ORDER BY clause? Would adding an index on the computed score help, and if so, how should I approach that? Additional Information: The table test_mv_all_profiles is a materialized view with around 11M rows. We are using a rather old version of Postgres (9.6) so some newer features are not available to us in the mean time. The search_text field is a concatenation of multiple columns (like username, first name, and last_name). I already have a trigram index on search_text for the similarity search. I'm looking for advice on how to maintain performance while still being able to sort by the combined score. Any insights or recommendations would be greatly appreciated!

Trying to figure out why mysql's ORDER BY clause can return order differently, e.g.: The two files are both generated by using mysql on command line with a single command: mysql mydb -e 'SELECT DISTINCT name FROM my_table ORDER BY name;' > name.lst but on two different Linux systems, querying the same DB with apparently same mysql/MariaDB:

# Machine 1
$ mysql -V
mysql  Ver 15.1 Distrib 10.5.25-MariaDB, for Linux (x86_64) using  EditLine wrapper


# Machine 2
$ mysql -V
mysql  Ver 15.1 Distrib 10.5.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Where should I look next for what's causing the problem please? (Like my LANG? would it matter?) **UPDATE:** Comparing character set / collation variables completely as suggested by Akina revealed where the problem is. Here is the comparison between two systems https://www.diffchecker.com/DhsN55Wq/ Comparing with the two and consulting with https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql , I'm thinking that settings on the right make more sense; what'd you say? How to correct it please? thx.

I am currently trying to sort the results of two tables in such a way that the primary sort field is one in the child table but would like to keep the parent table results from being moved out of sequence. An example would be the following tables (using Ms SQL Server) : CREATE TABLE tier_a( idno INTEGER NOT NULL, name VARCHAR(10) NOT NULL ); CREATE TABLE tier_b( idno INTEGER NOT NULL, tier_a_idno INTEGER NOT NULL, name VARCHAR(10) NOT NULL ); INSERT INTO tier_a VALUES(3, 'C'); INSERT INTO tier_a VALUES(2, 'B'); INSERT INTO tier_a VALUES(1, 'A'); INSERT INTO tier_b VALUES(1, 1, 'A'); INSERT INTO tier_b VALUES(4, 2, 'C'); INSERT INTO tier_b VALUES(2, 1, 'B'); INSERT INTO tier_b VALUES(5, 3, 'A'); INSERT INTO tier_b VALUES(3, 1, 'C'); If I only sort by the child table : SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name FROM tier_a JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno ORDER BY tier_b_name ASC I get what I expect : Ascending by child tier_a_name | tier_b_name C | A A | A A | B A | C B | C Descending by child tier_a_name | tier_b_name B | C A | C A | B C | A A | A But what I am trying to achieve is to still keep the ordering of the parent table : Ascending by child tier_a_name | tier_b_name A | A A | B A | C C | A B | C Descending by child tier_a_name | tier_b_name A | C A | B A | A B | C C | A I can simulate it with a terrible manual ORDER BY : Ascending by child SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name FROM tier_a JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno ORDER BY CASE -- Parent of the Lowest tier_b name (A) WHEN tier_a.idno = 1 THEN 1 -- Parent of the Lowest tier_b name (A) but Higher tier_a idno WHEN tier_a.idno = 3 THEN 2 -- Parent of the Highest tier_b name (C) WHEN tier_a.idno = 2 THEN 3 END, tier_b_name ASC Descending by child SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name FROM tier_a JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno ORDER BY CASE -- Parent of the Highest tier_b name (C) WHEN tier_a.idno = 1 THEN 1 -- Parent of the Highest tier_b name (C) but Higher tier_a idno WHEN tier_a.idno = 2 THEN 2 -- Parent of the Lowestest tier_b name (A) WHEN tier_a.idno = 3 THEN 3 END, tier_b_name DESC

I have a table (called sentences) in which I have some sentences. I want people to be able to search it with multiple words, which would make the SQL query look like this: SELECT content FROM sentences WHERE content LIKE "%%" OR content LIKE "%%" OR content LIKE "%%" etc... ; How can I sort the results by the number of occurrences of different words, the most matching sentence comes first, etc...?

I have a table with data something like below:

| ID(pk) | name | count |
|--------|------|-------|
| id1    | xyz  |    98 |
| id2    | ptr  |     5 |
| id3    | xyz  |     2 |
| id4    | de   |     1 |
| id5    | ptr  |     1 |

I want to first have the row with max count then all the rows with same name sorted by count. After that next max count and all the counts with same name. Something like below:

| ID(pk) | name | count |
|--------|------|-------|
| id1    | xyz  |    98 |
| id3    | xyz  |     2 |
| id2    | ptr  |     5 |
| id5    | ptr  |     1 |
| id4    | de   |     1 |

Is something like this possible in Postgres?

I need to sort a json item in a record, so here are two examples: # First case

create table jsontable (
    jsonitem json
);

Next I insert a json item:

insert into jsontable(jsonitem) values ('{ "3" : "foo", "2" : "bar", "1" : "qux" }');

Then I query the values:

select t.jsonitem from jsontable t;
                 jsonitem                  
-------------------------------------------
 { "3" : "foo", "2" : "bar", "1" : "qux" }
(1 row)

Let's suppose the key is unique and is an integer > 0; **Question 1:** ¿ How to sort the json item values by the key value, and get the following ?

{ "1" : "qux", "2" : "bar", "3" : "foo" }

# Second case This case use named keys and named values.

create table jsontable_arr(
   jsonitem json
   );

insert into jsontable_arr(jsonitem) values ('[  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]');

select t.* from jsontable_arr t;
                                                jsonitem
--------------------------------------------------------------------------------------------------------
 [  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]
(1 row)

**Question 2:** ¿ How to sort the json array item by the key value, and get the following?

[  { "key" : "1" , "value": "qux"}, { "key" : "2" , "value": "bar"}, {"key" : "3" , "value": "foo"} ]

Thanks for your suggestions.