need query by MAX date

Hello All,
I have the following table structure:
CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
[Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Permit_ID] [int] NOT NULL ,
[Status_Type] [tinyint] NOT NULL ,
[Status_Date] [datetime] NOT NULL
) ON [PRIMARY]
My data is:
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
I need to make a query to get out a recordset that is the entire row for
the MAX(Status_Date) for that Permit_ID, so like this:
Status_ID Permit_ID Status_Type Status_Date
2 816 2 '4/2/2005'
4 817 2 '4/4/2005'
5 818 1 '4/1/2005'
7 819 2 '4/2/2005'
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***Try,
select [Status_ID], [Permit_ID], [Status_Type], [Status_Date]
from [dbo].[tbl_PP_PermitStatusDates] as a
where [Status_Date] = (select max(b.[Status_Date]) from
[dbo].[tbl_PP_PermitStatusDates] as b where b.[Permit_ID] = a.[Permit_ID])
AMB
"Mij" wrote:

> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***
>|||On Fri, 01 Apr 2005 13:25:54 -0800, Mij wrote:
(snip)

>I need to make a query to get out a recordset that is the entire row for
>the MAX(Status_Date) for that Permit_ID, so like this:
Hi Mia,
Thanks for posting CREATE TABLE and INSERT statements. You can use:
SELECT Status_ID, Permit_ID, Status_Type, Status_Date
FROM tbl_PP_PermitStatusDates AS a
WHERE Status_Date =
(SELECT MAX(Status_Date)
FROM tbl_PP_PermitStatusDates AS b
WHERE b.Permit_ID = a.Permit_ID)
ORDER BY Status_ID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||That does seem to work. Thanks.
Mia J.
*** Sent via Developersdex http://www.examnotes.net ***|||Hello Mij,
This one works fine :
select (select Status_ID
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusID,
A.Permit_ID ,
(select Status_Type
from tbl_PP_PermitStatusDates B
WHERE B.Status_Date = MAX(A.Status_Date)
and B.Permit_ID = A.Permit_ID) StatusType,
MAX(A.Status_Date)
from tbl_PP_PermitStatusDates A
Group by A.Permit_ID
Thanks,
Gopi
"Mij" <mdsj@.infi.net> wrote in message
news:uJxBkFwNFHA.2392@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I have the following table structure:
> CREATE TABLE [dbo].[tbl_PP_PermitStatusDates] (
> [Status_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Permit_ID] [int] NOT NULL ,
> [Status_Type] [tinyint] NOT NULL ,
> [Status_Date] [datetime] NOT NULL
> ) ON [PRIMARY]
> My data is:
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(816,2,'4/2/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(817,2,'4/4/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(818,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,1,'4/1/2005')
> INSERT dbo.tbl_PP_PermitStatusDates VALUES(819,2,'4/2/2005')
> I need to make a query to get out a recordset that is the entire row for
> the MAX(Status_Date) for that Permit_ID, so like this:
> Status_ID Permit_ID Status_Type Status_Date
> 2 816 2 '4/2/2005'
> 4 817 2 '4/4/2005'
> 5 818 1 '4/1/2005'
> 7 819 2 '4/2/2005'
> Mia J.
> *** Sent via Developersdex http://www.examnotes.net ***