CODEWITHSUNDEEP
sqlpostgresqlsorting
jfreak53
jfreak53

Reputation: 2349

PostgreSQL Order by stepping numbers

I need to order records from a table by a column. The old system the customer was using manually selected level 1 items, then all the children of level 1 items for level 2, then so on and so forth through level 5. That is horrible IMHO, as it requires hundreds of queries and calls to the DB.

So in the new DB structure I'm trying to make it all one query to the DB if possible and have it order it correctly the first time. The customer wants it displayed to them this way so I have no choice but to figure out a way to order this way.

This is an example of the items and their level codes (1 being the single digit codes, 2 the 2 digit codes, 3 for 4 digit codes, 4 for 6 digit codes and level 5 for 8 digit codes):

enter image description here

It's supposed to order basically everything that starts with a 5 goes under Code 5. Everything that starts with a 51 goes under code 51. If you look at the column n_mad_id it links to the "Mother" ID of the code that is the mother of that code, so code 51's mother is code 5. Code 5101's mother is code 51. Code 5201's mother is code 52. And so on and so forth.

Then the n_nivel column is the level that the code belongs to. Each code has a level and a mother. The top level codes (i.e. 1, 2, 3, 4, 5) are all level 1 since they are only one digit.

I was hoping that there might be an easy ORDER BY way to do this. I've been playing with it for two days and can't seem to get it to obey.

Upvotes: 0

Views: 118

Answers (2)

Abelisto
Abelisto

Reputation: 15614

Interesting task. As I understand that you want to get result in order like

n_id   n_cod n_nivel n_mad_id  
  10       5       1        0
  11      51       2       10
  12    5101       3       11
  14  510101       4       12
...
  13      52       2       10
...

?

If yes then it may do the trick:

with recursive 
  tt(n_id, n_mad_id, n_cod, x) as (
    select t.n_id, t.n_mad_id, t.n_cod, array[t.n_id] 
    from yourtable t where t.n_mad_id = 0
    union all
    select t.n_id, t.n_mad_id, t.n_cod, x || t.n_id 
    from tt join yourtable t on t.n_mad_id = tt.n_id)
select * from tt order by x;

Here is my original test query:

create table t(id, parent) as values
  (1, null),
    (3, 1),
      (7, 3),
      (5, 3),
        (6, 5),
  (2, null),
    (8, 2), 
    (4, 2);

with recursive 
  tt(id, parent, x) as (
    select t.id, t.parent, array[t.id] from t where t.parent is null
    union all
    select t.id, t.parent, x || t.id from tt join t on t.parent = tt.id)
select * from tt order by x;

and its result:

 id | parent |     x     
----+--------+-----------
  1 | (null) | {1}
  3 |      1 | {1,3}
  5 |      3 | {1,3,5}
  6 |      5 | {1,3,5,6}
  7 |      3 | {1,3,7}
  2 | (null) | {2}
  4 |      2 | {2,4}
  8 |      2 | {2,8}
(8 rows)

Read about recursive queries.

Upvotes: 0

Patrick
Patrick

Reputation: 32199

The absolutely simplest way would be to cast the n_cod field to text and then order on that:

SELECT *
FROM mytable
WHERE left(n_cod::text, 1) = '5' -- optional
ORDER BY n_cod::text;

Not pretty, but functional.

You could consider changing your table definition to make n_cod of type char(8) because you do not use it as a number anyway (in the sense of performing calculations). That would make the query a lot faster.

Upvotes: 1

Related Questions