Does sort like "order by" in sql for list of fields?

[Jackiifilwhh]

Hi, I'm new to Splunk. The question I want to ask is does sort like "order by" in sql for list of fields, which divide into groups first and then sort within group. For example :

no	time
1	2022-01-22 18:00:00.000
2	2022-01-20 18:00:00.000
2	2022-01-26 18:00:00.000
1	2022-01-21 18:00:00.000

When in sql, using command "order by no, time desc", the result is like this :

no	time
1	2022-01-22 18:00:00.000
1	2022-01-21 18:00:00.000
2	2022-01-26 18:00:00.000
2	2022-01-20 18:00:00.000

But in SPL, when I use command "sort str(no), -str(time)", the result is this :

no	time
2	2022-01-26 18:00:00.000
1	2022-01-22 18:00:00.000
1	2022-01-21 18:00:00.000
2	2022-01-20 18:00:00.000

Is sort different from order by in sql or just my command is wrong? Thank you very much for answering my question! 

[tshah-splunk]

Hi @Jackiifilwhh,

Can you try using the field directly in the sort function instead of converting it to string? Although, you will need to convert time to string using time converter functions. Below is the sample query you can try out.

| eval Time=strftime(time, "%Y-%m-%d %H:%M:%S.%Q") 
| table no Time
| sort no - Time

[Jackiifilwhh]

Hi @tshah-splunk 

thank you and it worked finally by using table command after sort!

the truth is sort is same as order by in sql!

[gcusello · Accepted solution]

Hi @Jackiifilwhh,

you should see the sort command at https://docs.splunk.com/Documentation/SCS/current/SearchReference/SortCommandExamples

you can use the sort command adding a field and not a function, to have the desc, you have to use "-".

in your case

| sort no -time

Ciao.

Giuseppe

[Jackiifilwhh]

Hi @gcusello 

thank you very much!

sort is same as "order by" and I know why it seems doesn't work firstly

I use command "table" first and then use "sort", so it won't work!

when I reverse these two commands, it worked successfully!

[gcusello]

Hi @Jackiifilwhh,

at first put attention to the field names: the are case sensitive ("Time" is different by "time") and if you're using the rename command you have to use the renamed fieldname.

In addition, to have a descendant order, you have to put the "-" attached to the field without spaces, in other words:

|  sort - time       isn't correct

| sort -time         is correct

If you could share your search I could check it.

Ciao.

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors 😉

[Jackiifilwhh]

Hi @gcusello 

here is my search. Btw, my Splunk's version is 8.1.4

eval time=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q") | sort 0 no, -time | table no time

[gcusello]

Hi @Jackiifilwhh.

when you want to sort for a date or a time, it's always better to sort in epochtime, in other words change the order of your commands.

In addition don't use comma in sort command.

| sort 0 no -_time 
| eval time=strftime(_time,"%Y-%m-%d %H:%M:%S.%Q") 
| table no time

 Ciao.

Giuseppe

[Jackiifilwhh]

Hi @gcusello 

Well, thank you! This is really a better way😁.