SQL Distinct - Basic Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Distinct - Basic Question

Author

Topic

brettrg
Starting Member

7 Posts

Posted - 2005-11-14 : 18:14:17

I have a table of requests and a related table of requests_assigned(joined by requestID). What i want is for my sproc to return the most recent record of the request in the assigned table. The primary key of the assigned requests is RequestID + DateAssigned.

So for example, if RequestID 17 was assigned on one date to PersonA and then more recently to PersonB, i just want my query to return the record for PersonB.

Is this related to a SELECT DISTINCT? I'm clearly a rook

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-11-14 : 19:04:36

Use the MAX function on the datetime stamp of the assignment, then GROUP BY the requestID.

select r.request_id, max(ra.date_created)
from @request r 
inner join @request_assigned ra
	on r.request_id = ra.request_id
group by r.request_id

Nathan Skerl

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-14 : 19:09:04

select t1.*
from tbl t1
join (select RequestID, DateAssigned = max(DateAssigned) from tbl group by RequestID) t2
on t1.RequestID = t2.RequestID
and t1.DateAssigned = t2.DateAssigned

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

brettrg
Starting Member

7 Posts

Posted - 2005-11-14 : 19:25:56

quote:
Originally posted by nathans

Use the MAX function on the datetime stamp of the assignment, then GROUP BY the requestID.

select r.request_id, max(ra.date_created)
from @request r
inner join @request_assigned ra
on r.request_id = ra.request_id
group by r.request_id

Nathan Skerl

That code worked great for me but the only problem is i'm joining three or four other tables......soon as i do that it gives me multiple records again.

Do I need to use max for every field other than requestID?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-11-14 : 19:42:37

I think it would be a lot easier if you posted a table create script, some sample data, and the desired results for us.

Please check this link out for some guidelines:

[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

Thanks!

Nathan Skerl

brettrg
Starting Member

7 Posts

Posted - 2005-11-15 : 10:15:56

Ok, here is my create table script for both tables:

CREATE TABLE [dbo].[Requests] (
[RequestID] [int] IDENTITY (1, 1) NOT NULL ,
[Department] [tinyint] NOT NULL ,
[System] [tinyint] NOT NULL ,
[Type] [tinyint] NOT NULL ,
[DateSubmitted] [smalldatetime] NOT NULL ,
[DateNeeded] [smalldatetime] NULL ,
[Description] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Status] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Requests] WITH NOCHECK ADD
CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED
(
[RequestID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests_Assigned] (
[RequestID] [int] NOT NULL ,
[Owner] [tinyint] NOT NULL ,
[DateAssigned] [smalldatetime] NOT NULL ,
[AssignedBy] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Requests_Assigned] WITH NOCHECK ADD
CONSTRAINT [PK_Requests_Assigned] PRIMARY KEY CLUSTERED
(
[RequestID],
[DateAssigned]
) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

brettrg
Starting Member

7 Posts

Posted - 2005-11-15 : 10:18:40

Here is the stored procedure script:

CREATE PROCEDURE dbo.SP_Get_Requests_Assigned
AS SELECT r.RequestID, MAX(ra.DateAssigned) AS DATEASSIGNED, r.Department, d.DeptName, r.System, s.SystemName, r.Type, t.Type AS TypeName,
r.DateSubmitted, r.DateNeeded, r.ContactName, r.ContactEmail, r.Priority, r.Status, dbo.Owners.FName AS Owner
FROM dbo.Requests r INNER JOIN
dbo.Departments d ON r.Department = d.DeptID INNER JOIN
dbo.Systems s ON r.System = s.SystemID INNER JOIN
dbo.Types t ON r.Type = t.TypeID INNER JOIN
dbo.Requests_Assigned ra ON r.RequestID = ra.RequestID INNER JOIN
dbo.Owners ON ra.Owner = dbo.Owners.OwnerID
WHERE (r.Status = 'A')
GROUP BY r.RequestID, r.Department, d.DeptName, r.System, s.SystemName, r.Type, t.Type, r.DateSubmitted, r.DateNeeded, r.ContactName, r.ContactEmail,
r.Priority, r.Status, dbo.Owners.FName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources