July 6, 2010 at 1:44 pm
I've been looking around for a means by which the most recent Child record can be selected for a specific Parent record, but I have not been able to adapt any of the examples that I've found.
Basically, the technique that I've seen uses the Row_Number() to assign a value to the child records sorted in DESC order and then selecting the record with a Row_Number() of 1.
The highest primary key of the child needs to be selected. The examples that I've found typically demonstrate using a date value.
Can anyone help get me started?
CREATE TABLE [dbo].[tblTrailerActivityHeaders](
[lngTrailerActivityHeaderId] [int] IDENTITY(1000,1) NOT NULL,
[txtTrailerDOTNumber] [nvarchar](15) NOT NULL,
[dteTrailerLoadDate] [datetime] NOT NULL,
[lngTrailerLoadLocation] [int] NULL,
[lngTrailerLoadStatus] [tinyint] NULL,
[dteTrailerSealDate] [datetime] NULL,
[txtTrailerSealId] [nvarchar](50) NULL,
[txtTrailerSealNumber] [int] NULL,
[dteTrailerDispatchDate] [datetime] NULL,
[lngTrailerDispatchLocation] [int] NULL,
[lngTrailerDispatchStatus] [tinyint] NULL,
[txtTrailerDispatchUserId] [nvarchar](50) NULL,
[dteTrailerUnloadDate] [datetime] NULL,
[lngTrailerUnloadLocation] [int] NULL,
[lngTrailerUnloadStatus] [tinyint] NULL,
[dblControlId] [float] NULL,
[lngTrailerDeliveryZoneId] [int] NULL,
[dteScheduledUnloadDateTime] [datetime] NULL,
[txtShowNumber] [nvarchar](8) NULL,
[lngTrailerLoadTypeId] [tinyint] NOT NULL,
[txtEntryUserId] [nvarchar](25) NULL,
[dteEntryDateTime] [datetime] NULL,
[txtModifiedUserId] [nvarchar](25) NULL,
[dteModifiedDateTime] [datetime] NULL,
[timestamp] [timestamp] NULL,
[intUsageTypeId] [tinyint] NOT NULL,
CONSTRAINT [PK_tblTrailerActivityHeaders] PRIMARY KEY CLUSTERED
(
[lngTrailerActivityHeaderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTrailerActivityHeaders] ADD DEFAULT ((0)) FOR [intUsageTypeId]
GO
/****** Object: Table [dbo].[tblTrailerUtilizationDetails] Script Date: 07/06/2010 14:18:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTrailerUtilizationDetails](
[lngTrailerUtilizationDetailsId] [int] IDENTITY(1000,1) NOT NULL,
[lngTrailerActivityHeaderId] [int] NOT NULL,
[lngTrailerLoadTypeId] [tinyint] NOT NULL,
[txtShowNumber] [nvarchar](8) NULL,
[lngContactId] [int] NOT NULL,
[lngClientId] [int] NOT NULL,
CONSTRAINT [PK_tblTrailerLoadUtilizationDetails] PRIMARY KEY CLUSTERED
(
[lngTrailerUtilizationDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblTrailerUtilizationDetails] ADD DEFAULT ((0)) FOR [lngContactId]
GO
ALTER TABLE [dbo].[tblTrailerUtilizationDetails] ADD DEFAULT ((0)) FOR [lngClientId]
GO
/****** Object: View [dbo].[vw_TrailerActivityHeaders_withUtilizationDetails] Script Date: 07/06/2010 14:18:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
INSERT INTO tblTrailerActivityHeaders
(txtTrailerDOTNumber, dteTrailerLoadDate, lngTrailerLoadTypeId)
SELECT
'10075' AS Expr1,
'2010-7-1' as Expr2,
0 as Expr3
INSERT INTO tblTrailerUtilizationDetails
(lngTrailerActivityHeaderId, lngTrailerLoadTypeId)
SELECT
IDENT_CURRENT('tblTrailerActivityHeaders'),
1 as Expr1
INSERT INTO tblTrailerUtilizationDetails
(lngTrailerActivityHeaderId, lngTrailerLoadTypeId)
SELECT
IDENT_CURRENT('tblTrailerActivityHeaders'),
2 as Expr1
July 6, 2010 at 2:04 pm
This will return the most recent detail row for a given HeaderId
select *
from tblTrailerUtilizationDetails
where lngTrailerActivityHeaderId = parentID
and lngTrailerUtilizationDetailsId = (select max(lngTrailerUtilizationDetailsId) from tblTrailerUtilizationDetails where lngTrailerActivityHeaderId = parentID)
The probability of survival is inversely proportional to the angle of arrival.
July 6, 2010 at 2:28 pm
I need the query to pull all parent records and each one's most recent child.
July 6, 2010 at 2:44 pm
here is psuedo-tsql to pull all parent rows and their most recent detail row:
select [columns you need] from
parent P
JOIN (select parentID, max(detailID) as newID from detail group by parentID) ZZ on P.parentID = ZZ.parentID
JOIN detail D on D.parentID = ZZ.parentID and D.detailID = ZZ.newID
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply