Question 390606 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Group equivalent row attributes together in SQL

I have a table that stores the same phone numbers but with different attributes.

Without changing the schema, I want to group up all the phones and sort them by their createdat and organizationid parameters.

Here is an example:
Unordered results:

id phone createdat audienceid organizationid
1 7877777777 Apr 2024 1 2
2 7877777777 May 2024 2 2
3 7877777777 Aug 2024 5 1
4 7877632794 Jan 2024 5 1
5 7877777777 Mar 2024 3 1

Ordered results:

id phone createdat audienceid organizationid
2 7877777777 May 2024 2 2
1 7877777777 Apr 2024 1 2
3 7877777777 Aug 2024 5 1
5 7877777777 Mar 2024 3 1
4 7877632794 Jan 2024 5 1

The order of the phone numbers or the organizationid’s are not important, what maters is that the phones are grouped together and sub grouped by organization, each sub-group is sorted based on the createdat date desc.

Right now I’d like to explore different methods of accomplishing this goal and if it’s more efficient if the phone numbers are only grouped and not ordered, same for the organizations.

Tags:

2

Answers


  1. 0

    I think what you’re asking for is an ORDER BY statement:

    ORDER BY phone ASC, createdat DESC, organizationid ASC

    Simply add it to the end of the query. You can list as many columns as you need at it will order them by those columns in that order of preference. Use ASC and DESC for ascending and descending respectively.

    Login or Signup to reply.
  2. 0

    For this sample data and expected result the correct order by is phone desc, organizationid desc, createdat desc:

    WITH    --  S a m p l e    D a t a :
    tbl (id, phone, createdat, audienceid, organizationid) AS
( Select 1, 7877777777, DATE '2024-04-01', 1, 2 Union All
  Select 2, 7877777777, DATE '2024-05-01', 2, 2 Union All 
  Select 3, 7877777777, DATE '2024-08-01', 5, 1 Union All 
  Select 4, 7877632794, DATE '2024-01-01', 5, 1 Union All
  Select 5, 7877777777, DATE '2024-03-01', 3, 1  )

    --    S Q L :
Select    *
From      tbl
Order By  phone desc, organizationid desc, createdat desc

    /*      R e s u l t :
id  phone       createdat    audienceid   organizationid
--  ----------  -----------  -----------  --------------
2   7877777777  2024-05-01             2               2
1   7877777777  2024-04-01             1               2
3   7877777777  2024-08-01             5               1
5   7877777777  2024-03-01             3               1  
4   7877632794  2024-01-01             5               1    */

    fiddle

    NOTE: It is unclear how same phone number could belong to different organizationid(s). I would expect that one organizationid has multiple phone numbers and that any particular phone number could belong to one and only one organizationid. If that is the case with real data – then to show the data in a way based on "what metters" would be organizationid, phone, createdat desc. This would keep together organizationid(s) with it’s phone numbers (kept together too) and create date descending as asked for.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search