CODEWITHSUNDEEP
sqlpostgresql
Muhamamd Awais
Muhamamd Awais

Reputation: 2385

SQL - Sort specific column based on another column

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.

Upvotes: 2

Views: 1212

Answers (2)

steloh
steloh

Reputation: 11

@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]

Upvotes: 0

MatheusOl
MatheusOl

Reputation: 11825

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).

Upvotes: 1

Related Questions