Maybe a FIRST aggregate function is needed? Help!
-
CREATE TABLE Projects (
ProjectNo int NOT NULL ,
IssueNo int NOT NULL ,
DateStarted datetime NULL ,
CONSTRAINT PK_Projects PRIMARY KEY CLUSTERED
(
ProjectNo,
IssueNo
) ON PRIMARY
) ON PRIMARY
GO
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1000, 1, '2006-01-03')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1000, 2, '2006-01-05')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1000, 3, '2006-01-01')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1000, 4, '2006-01-06')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1000, 5, '2006-01-01')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1001, 1, '2006-01-02')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1001, 2, '2006-01-01')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1001, 3, '2006-01-04')
INSERT INTO Projects (ProjectNo, IssueNo, DateStarted) VALUES (1001, 4, '2006-01-03')
ProjectNo IssueNo DateStarted
----------- ----------- -----------
1000 1 2006-01-03
1000 2 2006-01-05
1000 3 2006-01-01
1000 4 2006-01-06
1000 5 2006-01-01
1001 1 2006-01-02
1001 2 2006-01-01
1001 3 2006-01-04
1001 4 2006-01-03
How to produce the list as below in T-SQL:
(Basically just the first occurrence in the order of ProjectNo, DateStarted, IssueNo)
ProjectNo IssueNo DateStarted
----------- ----------- -----------
1000 3 2006-01-01
1001 2 2006-01-01
-
There is no FIRST() aggregate function, because the business rules defining "first" are so different. In your case, "First" means "earliest date".
In this case, you join to a derived table that gets the earliest date per project:
Select ProjectNo, IssueNo, DateStarted
From Projects As p
Inner Join
(
Select ProjectNo, Min(DateStarted) As EarliestDate
From Projects
) dtEarliest
On (p.ProjectNo = dtEarliest.ProjectNo And
p.DateStarted = dtEarliest.EarliestDate)
-
See if this (untested, unoptimized) works for your purposes.
SELECT
y.ProjectNo
,IssueNo = Min(x.IssueNo)
,y.DateStarted
FROM
dbo.Projects AS x
INNER JOIN
(
SELECT
ProjectNo
,DateStarted = Min(DateStarted)
FROM
Projects
GROUP BY
ProjectNo
 
AS y ONx.ProjectNo = y.ProjectNo
AND x.DateStarted = y.DateStarted
GROUP BY
y.ProjectNo
,y.DateStarted
-
Sorry for the dupe answer. PW's post wasn't there when I read the OP. Mine also ended up with a nasty smiley where the close parenthese should be.
-
thanks. you missed out the 'group by' so i included it as below:
Select p.ProjectNo, p.IssueNo, p.DateStarted
From Projects As p
Inner Join
(
Select ProjectNo, Min(DateStarted) As EarliestDate
From Projects
Group By ProjectNo
) As dtEarliest
On (p.ProjectNo = dtEarliest.ProjectNo And
p.DateStarted = dtEarliest.EarliestDate)
your sql statement produce the list below which is not correct:
ProjectNo IssueNo DateStarted
----------- ----------- -----------
1000 3 2006-01-01
1000 5 2006-01-01
1001 2 2006-01-01
problem is... an assumption is made whereby the easliestdate is unique which is not the case. the IssueNo field has been ignored.
I wonder if adding another derived table would solve the problem... i shall give it a try
-
ah, cheers!
think the 'Min' on the IssueNo may just do the trick.
not sure though how good it performs on big dataset...
-
cl, my code handles that issue if you want to use it. It's the same concept as PW's, but I noticed that you had two records with the same starting date so I accounted for it.
-
You can do without joins with a little bit of trickery:
SELECT ProjectNo,
CAST(SUBSTRING(MIN(CONVERT(VARCHAR,DateStarted,112) + IssueNo),9,10) AS INT) IssueNo,
MIN(DateStarted) DateStarted
FROM Projects
GROUP BY ProjectNo
By taking the minimum of DateStarted + IssueNo and strip off the IssueNo from right you can select the IssueNo corresponding to the minimum DateStarted!
Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply