SQL - Sort specific column based on another column

Question

I have data in following form, I wanted to sort the usernames column based on specfic user_id that I have to found from user_ids array.

 usernames     | empno    |   user_ids |       
------------------+---------------------
{ Akon, Den }  |    11    | { 12, 13 } |
{ John, Nash } |     7    | { 15, 12 } |
{ Ahmad, Umar }|     9    | { 18, 12 } |

Forexample, usernames with user_id = 12 should be shown first. Result would look like as below

 usernames     | empno    |       
------------------+--------
{ Akon, Den }  |    11    |
{ Nash, John } |     7    |
{ Umar, Ahmad }|     9    |

I am sure there would be some easiest way of doing this in Postgres. This structure is just an example.

Comma separated data isn't the SQL way, that design will only cause you lots of trouble... (And make easy things very difficult.) — jarlh
– jarlh, Commented Aug 29, 2016 at 12:49
@jarlh It is according to my database architecture I have to export some information in CSV because of have to do something like this. — Muhamamd Awais
– Muhamamd Awais, Commented Aug 29, 2016 at 12:52
@vkp according my architecture I can map one user_id to multiple usernames :) — Muhamamd Awais
– Muhamamd Awais, Commented Aug 29, 2016 at 12:53
Even if the business rule allows multiple user names for each user_id your db design is very poor. Search for database normalization. — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 29, 2016 at 13:01

MatheusOl · Accepted Answer · 2016-08-29 13:02:15Z

Well, the biggest issue here is that you are working with arrays, actually making things more difficult. If you really don't have normalized data in your database, you can use unnest function to make it row-by-row and array_agg to get the arrays back. If you are on 9.4+ it is easy as:

SELECT
    t.empno,
    array_agg(u.username ORDER BY u.user_id) AS username_agg,
    array_agg(u.user_id ORDER BY u.user_id) AS user_id_agg
FROM
    your_table t,
    unnest(t.usernames, t.user_ids) AS u(username, user_id)
GROUP BY
    t.empno
ORDER BY
    user_ids_agg

Before 9.4 you don't have LATERAL queries nor unnest with many parameters, so it would be a bit harder:

SELECT
    t.empno,
    array_agg(t.username ORDER BY t.user_id) AS username_agg,
    array_agg(t.user_id ORDER BY t.user_id) AS user_id_agg
FROM
    (
        SELECT
            t1.empno,
            unnest(t1.usernames) AS username,
            unnest(t1.user_ids) AS user_id
        FROM
            your_table t1
    ) t
GROUP BY
    t.empno
ORDER BY
    user_ids_agg

Both solutions assume that you have the same number of elements in both arrays for each row.

Please let me know if the code doesn't run (I haven't actually tried, so there may have been a typo or logic issue).

steloh · Accepted Answer · 2016-08-29 13:43:19Z

0

@MuhamamdAwais if you will only sort over the first element of the user_ids, try this: select usernames,empno from your_table order by user_ids[1]

edited Aug 29, 2016 at 13:43

answered Aug 29, 2016 at 13:10

steloh

113 bronze badges

Collectives™ on Stack Overflow

SQL - Sort specific column based on another column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related