SQL - How to select data row with Maximum value within a specific column?

Question

In the process of testing a SQL query in preparation of creating a view, I 1st came up with this formula

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'

The results gave me 2 rows:

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5000  |    22    | 14000 | 2018-06-11 00:00:00.000 | 2018-12-10 00:00:00.000 |
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

I wanted the 2nd row, whose ActStartDate is the largest to be the only one to appear.

| SchID | CourseID | EmpNo |       ActStartDate      |         ValidTo         |
--------------------------------------------------------------------------------
| 5022  |    22    | 14000 | 2018-08-08 00:00:00.000 | 2019-02-07 00:00:00.000 |

But due to the SchID having different numbers it ends up registering as separate entries. SchID is the only column that is shared among these 2 tables, so how do I tell SQL to ignore SchID reading and give me the display for just the 2nd row?

What is the DBMS? Tag it.

mohabbati
– mohabbati

2019-02-13 05:00:59 +00:00
Commented Feb 13, 2019 at 5:00 — mohabbati
– mohabbati, Commented Feb 13, 2019 at 5:00
Please provide the sample result you expect.

mohabbati
– mohabbati

2019-02-13 05:01:36 +00:00
Commented Feb 13, 2019 at 5:01 — mohabbati
– mohabbati, Commented Feb 13, 2019 at 5:01
testing using SSMS.

hjh93
– hjh93

2019-02-13 05:02:34 +00:00
Commented Feb 13, 2019 at 5:02 — hjh93
– hjh93, Commented Feb 13, 2019 at 5:02

Fahmi · Accepted Answer · 2019-02-13 05:03:39Z

2

You can try below - using subquery

select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo]
from [SQL3].[dbo].[Training_Record] a 
inner join

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.CourseID)as b
on a.SchID = b.SchID 

where EmpNo = '141281' and CourseID = '22' and 
b.ActStartDate in (select max([ActStartDate]) from [SQL3].[dbo].[Training_Schedule] z1 where b.courseid=z1.courseid group by z1.courseid)

OR You can try using row_number()

select * from 
(
select a.SchID as [SchID],  b.CourseID as [CourseID], a.EmpNo as [EmpNo], 
   b.ActStartDate as [ActStartDate], a.ValidTo as [ValidTo],row_number() over(partition by b.courseid order by b.ActStartDate desc) as rn
from [SQL3].[dbo].[Training_Record] a inner join 

(select z.schid, z.CourseID as [CourseID],max(z.ActStartDate) as [ActStartDate]
from [SQL3].[dbo].[Training_Schedule] z group by z.SchID, z.CourseID)as b

    on a.SchID = b.SchID 

-- to test data content
where EmpNo = '141281' and CourseID = '22'
)A where rn=1

answered Feb 13, 2019 at 5:03

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hjh93 Over a year ago

Wow... Never thought to use an SQL statement to get the specific value before. Thanks.

hjh93 Over a year ago

But say for example I want the table to show me all data with unique CourseID & EmpNo combination with the max ActStartDate. What should I do then?

hjh93 Over a year ago

I see. Then I have another bigger question related to this, as this was phase 1 of the view construction. I finished the final phase and I got some issues to ask. I'll post it in another question. Mind checking it out? Thanks.

Collectives™ on Stack Overflow

SQL - How to select data row with Maximum value within a specific column?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related