Search code examples
sqldatabasepostgresqlgroup-byrow

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

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

Solution

  • 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