This is the below data in my Table1
BID PID TIME --------------+-------------------------+-------------------------------- 1345653 330760137950 2012-07-09 21:42:29 1345653 330760137950 2012-07-09 21:43:29 1345653 330760137950 2012-07-09 21:40:29 1345653 330760137950 2012-07-09 21:41:29 1345653 110909316904 2012-07-09 21:29:06 1345653 221065796761 2012-07-09 19:31:48 So If I need to clarify the above scenario- I have data in above table like this- For USER *`1345653` *I have this PID `*330760137950` *four times but with different timestamps in red color. So I need the output something like this- Output that I need:- *1345653 330760137950 2012-07-09 21:43:29 * *1345653 330760137950 2012-07-09 21:42:29 * *1345653 330760137950 2012-07-09 21:41:29* 1345653 110909316904 2012-07-09 21:29:06 1345653 221065796761 2012-07-09 19:31:48 So Basically If BID and PID are same but with different timestamps, then I need TOP 3 sorted with TIME in descending order And for this I created rank UDF (User Defined Function). And I wrote the below query but its not working for me. Can anyone help me on this? * * * * *SELECT buyer_id, item_id, created_time* * FROM table1* * where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) = '2012-07-09' AND rank(buyer_id) < 3* * DISTRIBUTE BY buyer_id* * SORT BY buyer_id, created_time desc*
