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

user_10079875 0
Viewed 106

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: enter image description here

Please suggest any solution in sql. Thank you!

View on DB Fiddle

row group-by postgresql sql database
1 Answers

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

user_100798750
Related