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, Aug 29 '16 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, Aug 29 '16 at 12:52
@vkp according my architecture I can map one user_id to multiple usernames :) — Muhamamd Awais, Aug 29 '16 at 12:53
This simply means you have your map wrong. You either need to change the direction of your references or use an additional table. Can you provide the structure of relevant tables, please? What is `user_ids` supposed to reference? — Boris Schegolev, Aug 29 '16 at 13:00
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, Aug 29 '16 at 13:01
Why is `Umar Ahma` sorted _after_ `John, Nash`? Both have `user_id = 12` - what is the criteria to sort one before the other? — , Aug 29 '16 at 13:15
@a_horse_with_no_name in fact, the order must be applied IN the first column ({John, Nash} becomes {Nash, John}). The rows order doesn't seem to be the problem. — Raphaël Althaus, Aug 29 '16 at 13:17
Then this seems a duplicate of: http://stackoverflow.com/q/24987547/330315 or http://stackoverflow.com/questions/2913368/sorting-array-elements or http://stackoverflow.com/questions/17991423/postgresql-sort-array-by-words-in-each-elements or similar questions: http://stackoverflow.com/search?q=%5Bpostgresql%5D+sort+array — , Aug 29 '16 at 13:25

score 1 · Answer 1 · answered Aug 29 '16 at 13:02

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 · Answer 2 · 2016-08-29T13:43:19.923

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 '16 at 13:43

answered Aug 29 '16 at 13:10

steloh

11
3

SQL - Sort specific column based on another column

2 Answers2