I have a 2 tables subject_master and slot_master, one subject have multiple slots, I want to run a query that should ruturns the most coming slot , it should retunr the distinct values only e.g . I have following 2 tables

Table1 : subject_master

Table 2 : slot_master

I want a query that return the expected outoput if current date is 30-07-2022 and Current time is 05:00

I am not good in explaning, I am just trying to fetch the most recent coming slot

CodePudding user response：

You can use ROW_NUMBER and an INNER JOIN

WITH CTE As (SELECT 
      "subject_id"
      , "date"
      , "start_time"
      , ROW_NUMBER() OVER(PARTITION BY "subject_id" ORDER BY  "date", "start_time") rn
FROM slot_master 
WHERE DATE("date") > DATE('2022-07-31'))
SELECT       CTE."subject_id"
,subject_name
      , "date"
      , "start_time" FROM CTE  INNER JOIN  subject_master ON cTE.subject_id = subject_master.subject_id
WHERE subject_master.status = 'active' AND rn = 1

subject_id    | subject_name | date       | start_time
:------------ | :----------- | :--------- | :---------
UHGAwlfrGDFwU | Maths        | 02-08-2022 | 06:00     
X3Sv7nE5HqhXg | Physics      | 01-08-2022 | 09:00     
Y7fHY4mEJmHaS | Chemistry    | 02-08-2022 | 06:00     

db<>fiddle here

CodePudding user response：

In PostgreSQL, you can use select distinct on to accomplish this. So long as the date is of type date and start_time is of type time, this will work:

select distinct on (s.subject_id) s.subject_id, s.subject_name, slot.date, slot.start_time
  from subject_master s
       join slot_master slot
         on slot.subject_id = s.subject_id
 where slot.date   slot.start_time >= '2022-07-30 05:00'
 order by s.subject_id, slot.date, slot.start_time;