CODEWITHSUNDEEP
sqlt-sql
kazzi
kazzi

Reputation: 534

Get first appointment on a given day

I can pull a list of appointments that took place, using the following query:

SELECT
   host
  ,name
  ,apt_time
FROM
   table
ORDER BY apt_time

And I will get:

host   name   apt_time
sam    bob    2017-01-01 08:00:00.000
sam    joe    2017-01-01 12:00:00.000
sam    tim    2017-01-05 09:00:00.000
sam    kay    2017-01-05 14:00:00.000

However, I just want to see the first appointment that the host saw each day, to get something like:

host   name   apt_time
sam    bob    2017-01-01 08:00:00.000
sam    tim    2017-01-05 09:00:00.000

What's a clean way to just the first appointment?

Upvotes: 0

Views: 169

Answers (3)

lathspell
lathspell

Reputation: 3310

WITH 
 tmp1 AS (
   SELECT host, name, to_char(apt_time, 'YYYY-mm-dd') as apt_day, apt_time FROM logins
 ),
 tmp2 AS (
   SELECT host, name, apt_day, row_number() over (partition by host, apt_day order by apt_time asc) as p_rank FROM tmp1
 )
SELECT host, apt_day, name FROM tmp2 WHERE p_rank = 1;

 host |  apt_day   | name
------+------------+------
 foo  | 2017-01-05 | kay
 sam  | 2017-01-01 | bob
 sam  | 2017-01-05 | tim
(3 Zeilen)

(edit: changed rank() to row_number() as the latter could return more than one row if the apt_date is identical which is unlikely but theoretically possible)

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81950

One option is using the WITH TIES clause in concert with Row_Number()

Select Top 1 with ties 
       host
      ,name
      ,apt_time
 From  YourTable
 Order By Row_Number() over (Partition By host,cast(appt_time as date) Order by appt_time)

Upvotes: 3

user8608031
user8608031

Reputation:

You can use ROW_NUMBER and PARTITION BY host name and the date part of the date time like this:

WITH CTE
AS
(
  SELECT
    host,name,apt_time,
    ROW_NUMBER() OVER(PARTITION BY host,CAST(apt_time AS DATE) 
                      ORDER BY apt_time) AS RN
  FROM table1
)
SELECT host, name, apt_time 
FROM CTE 
WHERE  RN =1

demo

| host | name |             apt_time |
|------|------|----------------------|
|  sam |  bob | 2017-01-01T08:00:00Z |
|  sam |  tim | 2017-01-05T09:00:00Z |

Upvotes: 2

Related Questions