SQL Custom unique Ordering with repeated sequence-CodePudding

I have a datetime column named time. I can best explain my issue with a example:

Example I've the following data in this column

1PM, 2PM, 3PM, 1PM, 1PM, 3PM, 2PM, 3PM, 2PM

If i do ORDER BY time, i get the following result:

1PM, 1PM, 1PM, 2PM, 2PM, 2PM, 3PM, 3PM, 3PM

But i want the result in this way:

1PM, 2PM, 3PM, 1PM, 2PM, 3PM, 1PM, 2PM, 3PM

How can we do this in SQL? I'm using postresql as my DB.

CodePudding user response：

One way you can try to use ROW_NUMBER window function with REPLACE function

SELECT time
FROM (
  SELECT *,REPLACE(time,'PM','') val,
       ROW_NUMBER() OVER(PARTITION BY REPLACE(time,'PM','')) rn
  FROM T
) t1
ORDER BY rn,val

CodePudding user response：

For example, sequence of the col a

with tbl(a, othercol) as
( 
   SELECT 1,1  UNION ALL
   SELECT 1,2  UNION ALL
   SELECT 1,3  UNION ALL
   SELECT 2,4  UNION ALL
   SELECT 2,5  UNION ALL
   SELECT 2,6  UNION ALL
   SELECT 3,7  UNION ALL
   SELECT 3,8  UNION ALL
   SELECT 3,9  
),
   cte as (
   SELECT *, row_number() over(partition by a order by a) rn
   from tbl
)
select a, othercol
from cte
order by rn, a

CodePudding user response：

The problem you have at hand is a direct result of not choosing the correct data type for the values you store.

To get the sorting correct, you need to convert the string to a proper time value. There is no to_time() function in Postgres, but you can convert it to a timestamp then cast it to a time:

order by to_timestamp("time", 'hham')::time

You should fix your database design and convert that column to a proper time type. Which will also prevent storing invalid values ('3 in the afternoon' or '128foo') in that column