-1

I have SQL query for filtering working time of employees. Actually I need to create a report(using visual studio reports) that displays arrival time for every employee and for each day in month. In my SQL I have a few records(arrival time) for the same date. Only I want to do is to show just first arrival time, not second and further. I called that column Start_Session as datetime2 type. How can I filter it, is it possible?

Thom A
  • 88,727
  • 11
  • 45
  • 75
red_devil
  • 1
  • 2
  • 1
    Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Nov 20 '20 at 10:59
  • Do you have duplicate rows or just several that you need the first of? – iamdave Nov 20 '20 at 11:00
  • @Larnu I tried that already, but in this case it isn't solution. When I write SELECT TOP 1 FROM table_name, my report doesn't have any records, I don't now why. – red_devil Nov 20 '20 at 11:20
  • @iamdave That isn't duplicates because I have different time for same date. For example, I have 2020-10-10 07:50:34 and 2020-10-10 07:57:23. Query shows me both, but I want just first, earlier time. But is it possible, because it is datetime2? – red_devil Nov 20 '20 at 11:23
  • *"When I write SELECT TOP 1 FROM table_name, my report doesn't have any records"* then there are no rows in the table. `TOP 1` doesn't filter the rows, just limits the number. – Thom A Nov 20 '20 at 11:23
  • @Larnu Result of query is empty. – red_devil Nov 20 '20 at 11:28
  • Yes, so the table `table_name` has no rows. @red_devil . `SELECT * FROM table_name` and `SELECT TOP (1) * FROM Table_name` will only return 0 rows if `table_name` has no rows. If the first returns rows, the second will return a single row. – Thom A Nov 20 '20 at 11:29
  • I want to display first(one) value for each employee. – red_devil Nov 20 '20 at 11:36
  • @Larnu If you want I can post screenshot of my report. So you can clearly see it. – red_devil Nov 20 '20 at 11:46
  • @Larnu Please actually take the time to engage with the people you speak with on here. You are running down a rabbit hole over how `top 1` works because the OP didn't actually read the answers in the question you linked, but responded to the text of your link. This is a misunderstanding that you have missed by moving too quickly and adds nothing but confusion. – iamdave Nov 20 '20 at 11:47
  • @iamdave if I wasn't engaging with the user, I wouldn't have wrote any comments; by writing a comment I am, by definition, engaging with them... The fact doesn't change they are saying `TOP 1` is causing them to get "no rows" which simply can't happen if the table contains rows. – Thom A Nov 20 '20 at 11:49
  • @Larnu Which is totally besides the point, as it has nothing to do with the problem in the question. – iamdave Nov 20 '20 at 11:55
  • No, the answer to the question is in my first comment, @iamdave : [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). (but how would I know that, I wasn't engaging the OP ) – Thom A Nov 20 '20 at 12:05

1 Answers1

1

It seems you aren't understanding the other question that Larnu linked you to in the comments. The answer to that question is not suggesting you want to be using a top(1), but instead filtering for the first row in a defined group. Per those answers, that can be achieved with the row_number() window function. In your case, this would look something like the following:

with r as
(
    select Employee
          ,cast(ArrivalTime as date) as ArrivalDate
          ,ArrivalTime
          ,row_number() over (partition by Employee, cast(ArrivalTime as date) order by ArrivalTime) as rn
    from YourTable
)
select Employee
      ,ArrivalDate
      ,ArrivalTime
from r
where rn = 1;
iamdave
  • 12,023
  • 3
  • 24
  • 53
  • Thanks a lot. I think this will help me. – red_devil Nov 20 '20 at 12:02
  • Your query is useful. But I need to display just one(first) value for each employee. For example I have two different values for same employee. And I want to display just first, earlier value. – red_devil Nov 23 '20 at 07:36
  • @red_devil Even if the values are on different days? At the moment, this query will give you the first `ArrivalTime` value for each `Employee` for each day. If you only want the first `ArrivalTime` across *all* days, then remove `, cast(ArrivalTime as date)` from the `partition by`. – iamdave Nov 23 '20 at 09:12
  • I fixed it. It is correct now. I made temporary database and this works. But in my main database I can't use DISTINCT keyword, because I have some attributes that are geography data type. And query can't be executed without DISTINCT. Any help? – red_devil Nov 23 '20 at 12:12
  • That sounds like a new question. If this question is answered as you appear to be saying, for the benefit of other users please mark it as the answer and then ask a new question with your `distinct` issue. – iamdave Nov 23 '20 at 12:26
  • I have problem with DISTINCT again. It doesn't work in my query. I tried GROUP BY but query can't be executed because I have two attributes with geography data type. When I run query SQL display message error that geography data type cannot be used in GROUP clause. @iamdave – red_devil Nov 24 '20 at 13:11
  • @red_devil As I said above, please mark this as the answer to the original question and then ask a new question for your `distinct` issue. – iamdave Nov 24 '20 at 13:12