Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Sort Unique Column with the most recent timestamp

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort Unique Column with the most recent timestamp
# 1  
Old 10-26-2015
Sort Unique Column with the most recent timestamp
Hello,

I have a sample file with the below contents :

Code:
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 03:50:06PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 01:15:56PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 02:15:21PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 03:15:17PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 02:18:32PM

I want only those output records satisying both of the below cases ::::::

1) Unique values from Column 2nd
2) Most recent timestamp from column 5th/6th.

For example following should be the Output (we have selected Unique values from column 2nd and the record with most recent timestamp is the output ):

Code:
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 03:50:06PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 03:15:17PM

Could someone please help me with providing approach to proceed with this.

Thanks
Rahul
# 2  
Old 10-26-2015
Any attempts from your side?

---------- Post updated at 12:40 ---------- Previous update was at 12:35 ----------

What system are you using?
Does it provide a date command providing
Code:
date -d"10/11/2015 03:50:06PM"
So 11. Okt 15:50:06 CEST 2015

Are month and day correctly interpreted?
This User Gave Thanks to RudiC For This Post:
rahul2662
# 3  
Old 10-26-2015
Hello RudiC,

We are using RedHat Server. Please find below the Output from the server where we will be running the script :

Code:
 ~]$ date -d"10/11/2015 03:50:06PM"
Sun Oct 11 15:50:06 SGT 2015
~]$

Thanks
Rahul
# 4  
Old 10-26-2015
Hello rahul2662,

Seems to be you are using GNU DATE, then following may help you in same.
Code:
awk '{DATE1=date -d $(NF-1) " " $NF;A[$2]=DATE1 > A[$2]?DATE1:A[$2];B[$(NF-1) OFS $NF]=$0} END{for(i in A){sub(/^0/,X,A[i]);print B[A[i]]};}' Input_file

Output will be as follows.
Code:
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 03:15:17PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 16:18:32PM

Where my Input_file is as follows, I added some more tests lines in it.
Code:
 cat Input_file
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 03:50:06AM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 01:15:56PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 02:15:21PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 03:15:17PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 02:18:32PM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 02:15:21PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 02:15:17AM
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 16:18:32PM

Thanks,
R. Singh
This User Gave Thanks to RavinderSingh13 For This Post:
rahul2662
# 5  
Old 10-26-2015
OK, changed my mind, date may not be needed. Try:
Code:
sort -r -k2,2 -k5.7,5.10 -k5.1,5.2n -k5.4,5.5n -k6.10 -k6 file4 | while read line; do FN=${line#*  }; FN=${FN%% *}; [ "$FN" != "$LAST" ] && echo $line; LAST=$FN; done
Backup  Oracle8_P112_PEGA_Archivedel    Completed       full    10/11/2015 03:50:06PM
Backup  Oracle8_G567_PEGA_Archivedel    Completed       full    10/11/2015 03:15:17PM

Make sure there's two spaces in ${line#* }
This User Gave Thanks to RudiC For This Post:
rahul2662
# 6  
Old 10-26-2015
Hi Ravinder,
please explain your code. Only small desc
Code:
awk '{DATE1=date -d $(NF-1) " " $NF;A[$2]=DATE1 > A[$2]?DATE1:A[$2];B[$(NF-1) OFS $NF]=$0} END{for(i in A){sub(/^0/,X,A[i]);print B[A[i]]};}' Input_file

Thanks,
# 7  
Old 10-26-2015
Hello looney,

Following may help you in same.
Code:
awk '{DATE1=date -d $(NF-1) " " $NF;        ######### making a variable here with name DATE1 whose value is equal to date -d $(NF-1) " " $NF, where date -d display time described by STRING, where that string is the value of $(NF-1) space $NF, where $(NF-1) is the second last field of Input_file and $NF is the LAST field of Input_file. Here is an example of this. echo "10/11/2015 03:50:06AM" | awk '{DATE1=date -d $(NF-1) " " $NF;print DATE1}' output will be 010/11/2015 03:50:06AM
A[$2]=DATE1 > A[$2]?DATE1:A[$2];            ######### making an array named A whose index is $2 and then checking a condition there if variable named DATE1's value is greater than value of A[$2] then make value of A[$2]=DATE1 else keep the value of A[$2] as it is. basically I am comparing the value of array named A[$2]'s value of it's previous date's values. So need to take the latest one as per user's request to made like this.
B[$(NF-1) OFS $NF]=$0}                      ######### Making an array named B whose index is $(NF-1) output field separator $NF's value, default OFS value is space. Keeping it's value to $0 which is complete line of the Input_file.
END{for(i in A){                            ######### Now starting END block here with a for loop means go through each element of array a here.
sub(/^0/,X,A[i]);                           ######### Removing initial 0 which is coming as you could see in STEP 2 here above.
print B[A[i]]};}                            ######### printing here value of array B whose index is actually value of array A so it will be B[A[i]].
' Input_file                                ######### Mentioning Input_file here.

Thanks,
R. Singh
These 3 Users Gave Thanks to RavinderSingh13 For This Post:
looney rahul2662 shekhar_4_u
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sort unique

Hi, I have an input file that I have sorted in a previous stage by $1 and $4. I now need something that will take the first record from each group of data based on the key being $1 Input file 1000AAA|"ZZZ"|"Date"|"1"|"Y"|"ABC"|""|AA 1000AAA|"ZZZ"|"Date"|"2"|"Y"|"ABC"|""|AA... (2 Replies)
Discussion started by: Ads89
2 Replies

2. Shell Programming and Scripting

Count occurrence of column one unique value having unique second column value

Hello Team, I need your help on the following: My input file a.txt is as below: 3330690|373846|108471 3330690|373846|108471 0640829|459725|100001 0640829|459725|100001 3330690|373847|108471 Here row 1 and row 2 of column 1 are identical but corresponding column 2 value are... (4 Replies)
Discussion started by: angshuman
4 Replies

3. UNIX for Dummies Questions & Answers

Print unique lines without sort or unique

I would like to print unique lines without sort or unique. Unfortunately the server I am working on does not have sort or unique. I have not been able to contact the administrator of the server to ask him to add it for several weeks. (7 Replies)
Discussion started by: cokedude
7 Replies

4. Shell Programming and Scripting

Unique sort with three fields

I have another file with three columns A,B,C as below 123,1,502 123,2,506 123,3,702 234,4,101 235,5,104 456,6,104 456,7,100 i want to sort such that i get a unique value in column A, and for those with multiple value in A, i want the lowest value in C. output should be Code:... (3 Replies)
Discussion started by: dealerso
3 Replies

5. Shell Programming and Scripting

Unique sort with two fields

I have a file with contents below 123,502 123,506 123,702 234,101 235,104 456,104 456,100 i want to sort such that i get a unique value in column A, and for those with multiple value in A, i want the lowest value in B. output should be 123,502 234,101 235,104 456,100 (3 Replies)
Discussion started by: dealerso
3 Replies

6. Shell Programming and Scripting

sort a file of 5 online by taking only the most recent

Hello, i develop a shell unix program and i have a csv file. every time we have 5 identical lines in the first 3 columns in the fourth column can be different and the 5th too. In the 5th I have a date in yyyy / mm / dd. I want to do a sort on the 5 lines and keep only the line where there is the... (7 Replies)
Discussion started by: papis
7 Replies

7. Shell Programming and Scripting

Awk sort and unique

Input file --------- 12:name1:|host1|host1|host2|host1 13:name2:|host1|host1|host2|host3 14:name3: ...... Required output --------------- 12:name1:host1(2)|host1(1) 13:name2:host1(2)|host2(1)|host3(1) 14:name3: where (x) - Count how many times field appears in last column ... (3 Replies)
Discussion started by: greycells
3 Replies

8. UNIX for Dummies Questions & Answers

sort biggest most recent files

if i am in /tmp file, and i have a few DIRs under /tmp. i want to find the biggest and most recent files (from 7 days ago) in /tmp and subfolders. (3 Replies)
Discussion started by: tjmannonline
3 Replies

9. Shell Programming and Scripting

ksh scripting: Extract 1 most recent record for unique key

I'm loading multiple delimited files into an Oracle DB using sqlldr on Unix. I would like to get only the most recent record per each unique key. There may be multiple updates for each key, but I only want the most recent one. There is a date column in my delimited files, so I'm using cat to... (2 Replies)
Discussion started by: OPTIMUS_prime
2 Replies

10. Shell Programming and Scripting

Sort and Unique in Perl

Hi, May I know, if a pipe separated File is large, what is the best method to calculate the unique row count of 3rd column and get a list of unique value of the 3rdcolum? Thanks in advance! (20 Replies)
Discussion started by: deepakwins
20 Replies
Login or Register to Ask a Question