CODEWITHSUNDEEP
sqlpostgresql
user5620899
user5620899

Reputation:

Select column values alternatively with predefined value order

I have a table with these values and i want to show result with alternative values and also i want to change the order of values like customer 1, customer 3 ,customer 2 but they must be in alternative order. customername .

    CREATE TABLE TEST (
      customername varchar(50)
    );

    INSERT INTO TEST VALUES('CUSTOMER 1');
    INSERT INTO TEST VALUES('CUSTOMER 1');
    INSERT INTO TEST VALUES('CUSTOMER 1');
    INSERT INTO TEST VALUES('CUSTOMER 2');
    INSERT INTO TEST VALUES('CUSTOMER 2');
    INSERT INTO TEST VALUES('CUSTOMER 2');
    INSERT INTO TEST VALUES('CUSTOMER 3');
    INSERT INTO TEST VALUES('CUSTOMER 3');
    INSERT INTO TEST VALUES('CUSTOMER 3');

Desired Result:

    CUSTOMER 2
    CUSTOMER 1
    CUSTOMER 3
    CUSTOMER 2
    CUSTOMER 1
    CUSTOMER 3
    CUSTOMER 2
    CUSTOMER 1
    CUSTOMER 3

I have tried: 

    SELECT  customername
    FROM    TEST
    ORDER BY ROW_NUMBER() OVER ( PARTITION BY customername ORDER BY  customername)

This returns,

    CUSTOMER 2
    CUSTOMER 1
    CUSTOMER 3
    CUSTOMER 2
    CUSTOMER 1
    CUSTOMER 3
    CUSTOMER 1
    CUSTOMER 3
    CUSTOMER 2

N.B Value can be integer like 3,2,1 because above is just an example

Upvotes: 3

Views: 229

Answers (1)

klin
klin

Reputation: 121574

You need an additional table which defines the desired order:

create table dict(customername varchar(50), priority int);
insert into dict values
('CUSTOMER 2', 1),
('CUSTOMER 1', 2),
('CUSTOMER 3', 3);

select customername
from test
join dict
using (customername)
order by
    row_number() over (partition by customername),
    priority;

 customername 
--------------
 CUSTOMER 2
 CUSTOMER 1
 CUSTOMER 3
 CUSTOMER 2
 CUSTOMER 1
 CUSTOMER 3
 CUSTOMER 2
 CUSTOMER 1
 CUSTOMER 3
(9 rows)

Upvotes: 1

Related Questions