Select query to return only the min datetime without group by?

Question

My table displays as below,

|_______________|____________|_____________________|______________________|
|  ENTRY POINT  |    NAME    |    DATE OF ENTRY    |   DATETIME OF ENTRY  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN    |  CHOCOTACO |     05/07/2018      | 05/07/2018 00:01:00  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 1  |  CHOCOTACO |     05/07/2018      | 05/07/2018 00:01:10  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 2  |  CHOCOTACO |     05/07/2018      | 05/07/2018 00:01:15  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 10 |  CHOCOTACO |     06/07/2018      | 06/07/2018 00:07:10  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 4  |  CHOCOTACO |     06/07/2018      | 06/07/2018 00:07:10  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 5  |  CHOCOTACO |     06/07/2018      | 06/07/2018 00:09:15  | 
|_______________|____________|_____________________|______________________|

Output expected is only the first value which has the min DATETIME OF ENTRY for each day as below,

|_______________|____________|_____________________|______________________|
|  ENTRY POINT  |    NAME    |    DATE OF ENTRY    |   DATETIME OF ENTRY  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN    |  CHOCOTACO |     05/07/2018      | 05/07/2018 00:01:00  | 
|_______________|____________|_____________________|______________________|
|    CHECKIN 10 |  CHOCOTACO |     06/07/2018      | 06/07/2018 00:07:10  | 
|_______________|____________|_____________________|______________________|

If I try to take the min DATETIME OF ENTRY, it probably makes me use a group by 'ENTRY POINT' and other column which basically gives me the same output as my input.

Please help me resolve this.

See answers to this post: https://stackoverflow.com/questions/3839982/row-with-minimum-value-of-a-column — nik7, May 07 '20 at 08:27
this link shows how to return first row, but i need my query to return first datetime of entry for each day. irrespective of entry point https://stackoverflow.com/questions/3839982/row-with-minimum-value-of-a-column, — hdhanarajan, May 07 '20 at 08:30

score 1 · Answer 1 · answered May 07 '20 at 08:31

1

You can use window function row_number(). here is the demo.

select
    ENTRY_POINT,   
    NAME, 
    DATE_OF_ENTRY,   
    DATETIME_OF_ENTRY
from    
(
    select
        *,
        row_number() over (order by DATETIME_OF_ENTRY) as rnk
    from myTable
) t
where rnk = 1

answered May 07 '20 at 08:31

zealous

7,336
4
16
36

if i try to duplicate the same data with different date, i need the query to return values for that as well. If i add a entry 06/07 1:00 and 07/07 1:00, it should return me three output like 05/07 1:00, 06/07 1:00 and 07/07 1:00 – hdhanarajan May 07 '20 at 08:40
@hdhanarajan I did not get you? – zealous May 07 '20 at 08:44
the above query only returns 05/07 1:00 which basically the same answer that I will get if i use limit as in https://stackoverflow.com/questions/3839982/row-with-minimum-value-of-a-column – hdhanarajan May 07 '20 at 08:45
@hdhanarajan did not you mention that you need only min value without using `min` ? – zealous May 07 '20 at 08:46
@hdhanarajan please update your question with expected result and cases you want to cover. also how adding different time can make your data duplicate. – zealous May 07 '20 at 08:53

score 1 · Accepted Answer · answered May 07 '20 at 09:08

You need window analytic functions of which DENSE_RANK() would suit well to include all ties(the records with the same [DATETIME OF ENTRY] values per each [NAME] and [DATE OF ENTRY] combination).

Need to find out the first record(s) of ascendingly ordered [DATETIME OF ENTRY] value grouped (partitioned) by each [NAME] and [DATE OF ENTRY] :

SELECT [ENTRY POINT], [NAME], [DATE OF ENTRY], [DATETIME OF ENTRY]
  FROM
 (
  SELECT T.*,
         DENSE_RANK() OVER 
              (PARTITION BY [NAME], [DATE OF ENTRY] ORDER BY [DATETIME OF ENTRY]) AS dr
    FROM T -- <your table>
  ) TT
  WHERE dr = 1;

Select query to return only the min datetime without group by?

2 Answers2