Search code examples
mysqlsqldata-manipulationdml

Sorting a complex records in MySQL table

I'm working on a personal project and I'm stuck in phase of cleaning a particular dataset. The dataset consists of records of an agent(having a unique AgentID) who visits different parking sites (having unique ParkingID). He is using an app to enter his records in the database. When agent enters into the parking site he presses the button for login and when he leaves he presses logout in the app (the agent can press the button multiple times, so there can be possibility of duplicate login and logout records). When the person visits a parking site, he/she can raise some tickets when he meets certain people at that place, thus marking Tickets Finished in the app. The problem is data that comes into the database is only sorted to minute stage. Thus, the records having same time can appear differently in the database. One such dummy dataset is shown below. Unsorted Table

I want to sort it together the event which finishes first should appear at top and the event that finishes later should come at the end. My desired output is shown below. Sorted Table

I'm having no idea how to sort this in MySQL, if anyone of you can help me in figuring out how to solve this problem then it would be really helpful from your side.

Solution

  • Sorting by a CASE WHEN to break the tie.

    SELECT *
FROM parking_agent_logs
ORDER BY `Date`, `Time`, ParkingID, AgentID
, CASE Event_Type 
  WHEN 'Log In' THEN 1
  WHEN 'Ticket Finished' THEN 2
  WHEN 'Log Out' THEN 3
  ELSE 9
  END

    Test on db<>fiddle here