Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Latest two rows for each group

I have two tables, one for storing project information and the other for project updates.

Table1
ProjID (key)
Proj_Title
Proj_Description

Table2
PProg_ProjID (foreign key to Table1 ProjID)
PProgID (key)
PProg_datetime

What I wish to retrieve is the last two progress updates (based on PProg_datetime) from Table2, for each ProjID in Table1.

This question is similar to Latest row for each group which discussed two different queries to produce the same desired result. Let's adapt those two queries to this question, but get the latest two rows for each group.

Here's the first query. The table names have been changed to match this particular question.

select Proj_Title
, Proj_Description
, PProg_datetime
from Table1
inner
join Table2 T
on ProjID = PProg_ProjID
where PProg_datetime
= ( select max(PProg_datetime)
from Table2
where PProg_ProjID = T.PProg_ProjID
)
or PProg_datetime
= ( select max(PProg_datetime)
from Table2
where PProg_ProjID = T.PProg_ProjID
and PProg_datetime
< ( select max(PProg_datetime)
from Table2
where PProg_ProjID = T.PProg_ProjID
)
)

The second query, involving a self-join, has to be extended to a third table. Note that a LEFT OUTER join is required, in case there is only one latest row in each group, not two:

select Proj_Title
, Proj_Description
, T1.PProg_datetime
from Table1
inner
join Table2 T1
on ProjID = PProg_ProjID
inner
join Table2 T2
on T1.PProg_ProjID = T2.PProg_ProjID
left outer
join Table2 T3
on T2.PProg_ProjID = T3.PProg_ProjID
and T2.PProg_datetime > T3.PProg_datetime
group
by Proj_Title
, Proj_Description
, T1.PProg_datetime
having T1.PProg_datetime = max(T2.PProg_datetime)
or T1.PProg_datetime = max(T3.PProg_datetime)

In Microsoft SQL Server and Access, there is yet another solution, using the TOP keyword:

select Proj_Title
, Proj_Description
, PProg_datetime
from Table1
inner
join Table2 T
on ProjID = PProg_ProjID
where PProg_datetime
in ( select top 2
PProg_datetime
from Table2
where PProg_ProjID = T.PProg_ProjID
order
by PProg_datetime descending
)

Much neater.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Data hub vs. data lake: Deciphering the differences

Data lakes and data hubs are approaches to data management that are typically opposed. Here are the main differences between ...

• ### Databricks Data Ingestion Network brings data to lakehouse

Databricks Ingest entered a public preview in a move by Databricks to enable a lakehouse that combines the best features of the ...

• ### Lessons learned from Credit Karma GraphQL architecture

Credit Karma's vice president of engineering explains why and how the personal finance service is using the GraphQL data query ...

• ### Gartner's Sallam: Top BI vendors poised for next disruption

Leading analytics vendors appear poised to remain innovative as AI becomes pervasive in BI, Gartner's Rita Sallam explains in a ...

• ### Gartner's Sallam: Top analytics platforms share common traits

In an interview, Gartner analyst Rita Sallam discusses what separates the top BI vendors from the rest as well as the trends that...

• ### Birst analytics key to partnership between Infor, Snowflake

A new partnership between Infor and Snowflake will use BI tools from Birst, acquired by Infor in 2017, to enable users to build ...

## SearchSAP

• ### Understand SAP ECC vs. HANA vs. S/4HANA vs. R/3

SAP ECC, HANA, S/4HANA and R/3 are all valuable tools in the IT world, but it's easy to get them confused. Learn about their ...

• ### 5 tips for a successful S/4HANA Cloud implementation

Moving to S/4HANA public cloud can help you save on IT maintenance and simplify real-time analysis. Here's a look at what you can...

• ### Take care of data before SAP S/4HANA migration

In this Q&A, Rajesh Rengarethinam of ERP security vendor Appsian discusses why reviewing data security and business processes are...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### Take advantage of these 5 benefits of server-side rendering

From performance improvements to browser compatibility, here are five reasons why you should use server-side rendering ...

• ### 5 tips to reduce webpage weight and improve user experience

A slow landing page makes a bad first impression for any potential website visitor. Here are five tips to improve the page and ...

• ### CSS performance optimization in 5 easy steps

Here are five CSS performance tips to help you avoid website performance problems.

## SearchDataCenter

• ### 5 considerations for proper mainframe decommissioning

As organizations decide to retire mainframe systems, admins should develop a plan that addresses application migration, program ...

• ### A look at software-defined networking upgrade guidelines

SDN brings the benefit of a mostly software-based update process. However, admins still must consider which updates are necessary...

• ### Plan for a successful ITIL implementation

Corporate buy-in and training are essential for organizations that decide to adopt the ITIL framework. Admins should also help ...

## SearchContentManagement

• ### Contentful App Framework first major release under new CEO

Contentful CEO Steve Sloan, who joined the company in November after leaving Twilio, discusses the release of App Framework, a ...

With new Google Drive integration, Adobe embeds free features for commenting and annotating PDFs inside Google Docs. Acrobat ...

• ### 4 benefits of business process automation

Companies are automating business processes to improve workflows and use technology effectively. Some benefits of BPA include ...

## SearchHRSoftware

• ### Coronavirus gets attention on Workday earnings call

The coronavirus is having an impact on Workday, prompting cancellation of a sales meeting in Orlando and placing a greater ...

• ### Cornerstone is building learning management's Netflix

Cornerstone's acquisition of Saba Software for \$1.4 billion takes out a learning management competitor and gives it a boost of ...

• ### 15 essential employee engagement survey questions

Crafting survey questions that uncover employee engagement issues is challenging. Here are some key questions and a guide to how ...

Close