Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – find unique record from table

Consider the following table:

Table Name: IN_OUT_RECORD

Query:

select DISTINCT  * 
from IN_OUT_RECORD AD 
where (AD_USER_ID = 'ST1301220007') 
AND (AD.AD_DATE BETWEEN '2024-06-01 00:00:00' AND ('2024-06-12 23:59:00'));
AD_ID AD_USER_ID AD_STATUS AD_DATE
11277 ST1301220007 IN 2024-06-01 09:10:40
11278 ST1301220007 OUT 2024-06-01 09:10:55
11279 ST1301220007 IN 2024-06-01 09:10:56
11280 ST1301220007 OUT 2024-06-01 18:21:23
11281 ST1301220007 IN 2024-06-05 09:20:21
11282 ST1301220007 OUT 2024-06-05 19:05:22
11283 ST1301220007 IN 2024-06-07 09:05:25
11284 ST1301220007 OUT 2024-06-07 18:05:25
11285 ST1301220007 IN 2024-06-07 18:06:25
11286 ST1301220007 OUT 2024-06-08 09:10:56
11287 ST1301220007 IN 2024-06-08 09:11:50

from here I want to retrieve only a single IN and OUT in a day.

for example:

AD_ID AD_USER_ID AD_STATUS AD_DATE
11277 ST1301220007 IN 2024-06-01 09:10:40
11280 ST1301220007 OUT 2024-06-01 18:21:23
11281 ST1301220007 IN 2024-06-05 09:20:21
11282 ST1301220007 OUT 2024-06-05 19:05:22
11283 ST1301220007 IN 2024-06-07 09:05:25
11284 ST1301220007 OUT 2024-06-07 18:05:25
11287 ST1301220007 IN 2024-06-08 09:11:50

Please help me to get the Correct Query.

Tags:

2

Answers


  1. 0

    As you seem to want to perserve the AD_ID, this is a bit trickier than a simple GROUP BY and UNION.

    You will need indicators which AD_DATE for AD_STATUS ‘IN’ is the first one, and which AD_DATE for AD_STATUS ‘OUT’ is the last one, per day.

    We can make these indicators this by using ROW_NUMBER() OVER (PARTITION BY .. ORDER BY ..). To partition by day, use the DATE() function to disregard the time.

    After this, you can select the relevant IN and OUT records per day, and UNION them, and potentially sort them by USER_AD, DATE(AD_DATE) and AD_STATUS.

    I will use some cte’s to show these steps:

    WITH status_per_day AS (
  SELECT 
    records.*,
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, AD_STATUS, DATE(AD_DATE) 
      ORDER BY AD_DATE ASC) AS first_time,  
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, AD_STATUS, DATE(AD_DATE) 
      ORDER BY AD_DATE DESC) AS last_time
  FROM records
), 

unioned_statuses AS (
  SELECT *
  FROM status_per_day
  WHERE AD_STATUS = 'IN' 
  AND first_time = 1
  UNION 
  SELECT *
  FROM status_per_day
  WHERE AD_STATUS = 'OUT' 
  AND last_time = 1
)
  
SELECT AD_ID, AD_USER_ID, AD_STATUS, AD_DATE
FROM unioned_statuses
ORDER BY AD_USER_ID, DATE(AD_DATE), AD_STATUS

    db<>fiddle here

    Login or Signup to reply.
  2. 0

    It’s going to be a bit more efficient than the other answer, if you keep the same ORDER BY clauses in the two window functions. You can hack LEAD to find if you are on the last row.

    WITH status_per_day AS (
  SELECT 
    records.*,
    ROW_NUMBER() OVER (
      PARTITION BY AD_USER_ID, DATE(AD_DATE), AD_STATUS 
      ORDER BY AD_DATE) AS rn,  
    LEAD(1) OVER (
      PARTITION BY AD_USER_ID, DATE(AD_DATE), AD_STATUS
      ORDER BY AD_DATE) AS is_not_last
  FROM records
)  
SELECT AD_ID, AD_USER_ID, AD_STATUS, AD_DATE
FROM status_per_day
WHERE (
  AD_STATUS = 'IN' AND rn = 1
  OR
  AD_STATUS = 'OUT' AND is_not_last IS NULL
)
ORDER BY
  AD_USER_ID,
  DATE(AD_DATE),
  AD_STATUS;

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search