Forum Discussion
Solved
Sort values by hours and days
| time_to_purchase |
| 0 - 6 hours |
| 1 - 3 days |
| 10 - 20 days |
| 12 - 18 hours |
| 18 - 24 hours |
| 20 - 30 days |
| 3 - 5 days |
| 30 - 45 days |
| 45 - 60 days |
| 5 - 10 days |
| 6 - 12 hours |
| 60 - 90 days |
| 90 - 180 days |
How do I sort time_to_purchase column? I want the data to look like below
0 - 6 hours
6 - 12 hours
12 - 18 hours
18 - 24 hours
1 - 3 days
3 - 5 days
5 - 10 days
10 - 20 days
20 - 30 days
30 - 45 days
45 - 60 days
60 - 90 days
90 - 180 days
I have tried sorting data from data tab-> sort->sort by time_to_purchase->sort by cell values-> sort by A-Z. But this didn't work. Can someone please advise?
Hi akshay2023,
You cannot sort this data directly since it has mutiple units. What you need to do is create another helper column for sorting purpose which will have all the data in single unit, i.e. everything in hours or everything in days.
Refer the below example where I've converted everything in days considering the upper end of the value. You can generate the values for this helper column either by typing manually or using formula to check hours/days in the main column.
Hi akshay2023,
You cannot sort this data directly since it has mutiple units. What you need to do is create another helper column for sorting purpose which will have all the data in single unit, i.e. everything in hours or everything in days.
Refer the below example where I've converted everything in days considering the upper end of the value. You can generate the values for this helper column either by typing manually or using formula to check hours/days in the main column.
nimesht Thanks! This solution works for me.
How about online sql？
create temp table aa as
select *,iif(instr(time_to_purchase,'hours'),0,1) type,regexp2('^\d+',time_to_purchase)*1 number from sort_time_to_purchase;
select time_to_purchase from aa order by type,number;
