How to group rows with same values in sql and don't disturb after applying order by?

Question

How to group rows with same values in sql and can't disturb after applying order by.

Schema (PostgreSQL v13)

CREATE TABLE IF NOT EXISTS products (
  id int NOT NULL,
  title varchar(200) NOT NULL,
  description varchar(200) NOT NULL,
  price int NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO products VALUES
  (1, 'test', 'test',2222),
  (2, 'test', 'test2',1111),
  (3, 'test3', 'test3',1111),
  (4, 'test3.2', 'test3.2',555),
  (5, 'test3.3', 'test3.3',1111),
  (6, 'test4', 'test4 desc',1111);

---

Query #1

SELECT DISTINCT
  tempId,pemId, 
  
  title,
  
  description,
  (CASE priceno WHEN 1 THEN price ELSE price END )AS price
  
FROM
(                   
    SELECT 
        
        
        ROW_NUMBER() OVER(PARTITION BY price) AS priceno,
        tempId,pemId,title,description,price
    FROM (
            select id as tempId,id as pemId,title,description,price from products
                group by 
                    
                    grouping sets((tempId,price),(tempId,pemId,title,description))
                
         ) As b
) As s
order by
    
    tempId, price asc;

tempid	pemid	title	description	price
1				2222
1	1	test	test
2				1111
2	2	test	test2
3				1111
3	3	test3	test3
4				555
4	4	test3.2	test3.2
5				1111
5	5	test3.3	test3.3
6				1111
6	6	test4	test4 desc

---

Expected Output:

Please suggest any solution in sql. Thank you!

View on DB Fiddle

Answer 1

Schema (PostgreSQL v13)

CREATE TABLE IF NOT EXISTS products (
  id int NOT NULL,
  title varchar(200) NOT NULL,
  description varchar(200) NOT NULL,
  price int NOT NULL,
  PRIMARY KEY (id)
);


INSERT INTO products VALUES
  (1, 'test', 'test',2222),
  (2, 'test', 'test2',1111),
  (3, 'test3', 'test3',1111),
  (4, 'test3.2', 'test3.2',555),
  (5, 'test3.3', 'test3.3',1111),
  (6, 'test4', 'test4 desc',1111);

---

Query #1

select id, 
title, 
description,
(CASE WHEN prno=1 THEN price::varchar ELSE '' END )AS price
from(
  select DISTINCT id, title, description, price, prno
from (
  select id, 1, null, null, null, price::varchar, 1 as prno from products
  union all
  select id, 2, id::varchar, title, description, price::varchar, 2 as prno from products
) temp1 (xid, xord, id, title, description,price,prno)
 order by price, prno
) as temp2;

id	title	description	price
			1111
2	test	test2
6	test4	test4 desc
3	test3	test3
5	test3.3	test3.3
			2222
1	test	test
			555
4	test3.2	test3.2

---

View on DB Fiddle