CODEWITHSUNDEEP
sqloracle-databasegroup-by
Mohsin Tester
Mohsin Tester

Reputation: 35

Select specific group of records with GROUP BY and MAX(DATE)

I am trying to enlist the agencies whose latest date of visit is Yes for each agency in the table, for example;

Agency Id   Date of Visit   Passed 
1           8/19/2015       No
1           6/9/2015        Yes
1           2/6/2015        No
2           9/2/2015        No
2           5/11/2015       Yes
2           3/4/2015        Yes
3           9/10/2015       Yes
3           5/11/2015       No
3           3/5/2015        No
4           10/6/2015       Yes
4           5/19/2015       No
4           3/25/2015       Yes

The desired result form the table should only be the following because in their latest date they were marked as Yes

Agency Id   Date of Visit   Passed 
3           9/10/2015       Yes
3           5/11/2015       No
3           3/5/2015        No
4           10/6/2015       Yes
4           5/19/2015       No
4           3/25/2015       Yes

I have tried using 

SELECT agencyid, max(dateofvisit), passed
FROM tblAgency
WHERE passed = 'Yes'
GROUP BY agencyid

But this does not seems to work as it bring all those records from the table which are passed as Yes.

Can somebody let me know if that is possible.

Upvotes: 0

Views: 80

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

Use an analytic function to get last value for "passed" column. Then it is easy:

select * 
from(
    select 
      agencyid,
      passed,
      first_value(passed) over (partition by agencyid order by dateofvisit desc) last_passed_value,
      dateofvisit
    from tblAgency
)
where last_passed_value = 'Yes';

You can also do it only with group bys and simple max, but you need some joins:

select b.* 
from(
    select 
      agencyid,
      max(dateofvisit) as max_dateofvisit
    from tblAgency
    group by agencyid
) lastentry
join tblAgency a on a.agencyid = lastentry.agencyid and a.dateofvisit=lastentry.dateofvisit
join tblAgency b on a.agencyid = b.agencyid 
where a.passed = 'Yes'

Upvotes: 1

jignesh
jignesh

Reputation: 1669

with below SQL query I got the expected result.Please reply me your feedback.

---create table
DECLARE @tblAgency TABLE
(
    AgencyId INT,
    dateofvisit DateTime,
    Passed Nvarchar(10)
)

---Insert Records
INSERT INTO @tblAgency VALUES(1,'2015/8/19','No')
INSERT INTO @tblAgency VALUES(1,'2015/6/9','Yes')
INSERT INTO @tblAgency VALUES(1,'2015/2/6','No')
INSERT INTO @tblAgency VALUES(2,'2015/9/2','No')
INSERT INTO @tblAgency VALUES(2,'2015/5/11','Yes')
INSERT INTO @tblAgency VALUES(2,'2015/3/4','Yes')
INSERT INTO @tblAgency VALUES(3,'2015/9/10','Yes')
INSERT INTO @tblAgency VALUES(3,'2015/5/11','No')
INSERT INTO @tblAgency VALUES(3,'2015/3/5','No')
INSERT INTO @tblAgency VALUES(4,'2015/10/6','Yes')
INSERT INTO @tblAgency VALUES(4,'2015/5/19','No')

DECLARE @PassedAgency Table
(
    AgencyId INT
)

--Select AgencyId with Passed='Yes' with Latest dateofvisit
INSERT INTO @PassedAgency
SELECT TM.AgencyId FROM @TblAgency AS TM
OUTER APPLY(SELECT MAX(TD.dateofvisit) AS MaxDate FROM @TblAgency AS TD WHERE TD.AgencyId=TM.AgencyId) A
WHERE TM.DateofVisit=A.MaxDate AND TM.Passed='Yes'


--select agency details
SELECT * from @TblAgency AS tbl1
LEFT JOIN @PassedAgency AS tbl2 ON tbl1.AgencyId=tbl2.AgencyId
WHERE tbl2.AgencyId IS NOT NULL ORDER BY tbl1.AgencyId,tbl1.dateofvisit desc

Upvotes: 0

Related Questions