January 6, 2004 at 12:27 am
Here is a query that is getting me general results:
CREATE PROCEDURE [dbo].[stpGetRBC]
@CNB varchar(25)
AS
SELECT DISTINCT
P.[P-N] AS P_N,
P.H_I AS H_I,
P.[P-R] AS P_R,
P.[B-L] AS B_L,
P.[I-T] AS I_T,
P.[P-NA] AS P_NA,
P.D AS P_D,
C.[C-N] AS C_N,
C.[C-NR] AS C_NR,
C.T AS C_T
FROM
P
INNER JOIN C ON (P.[C-N] = C.[C-N] )
WHERE
( UPPER( P.[I-T] ) = 'R' ) AND
( UPPER( C.[C-G] ) = 'YES' ) AND
(T = Upper(@CNB))
GO
This is working fine, but what I need to do is narrow down the results to get the first record for each P.[P-NA] So in the P table, there are multiple records for each unique P-NA field. For example, we could have a unique P-NA with the value of "spot" and attached to that could be 2 or more records with a P-NA value of "spot". What I want to do is only take the first record found where the P-NA value equals "spot" from the results of the stored proc above. So where I am getting 2 or more records for each unique P-NA value today, I want to just extract the first result. Also, the P.[P-N] field is a numeric value that could be used to declare a min value. Any help would be appreciated.
Thanks!
January 6, 2004 at 3:29 am
One way is to get into correlated sub-queries, but appart from being very complicated they are not very fast. A simpler way would be to create a temp table (by using #) to create a table with minimum P-N values then join this to your real tables.
For example:
select min([P-N]) as MinPN, P.[I-T] into #TempTbl from P group by P.[I-T]
(I assume P.[I-T] is your primary key)
Then
select P.[P-NA], P.[I-T], P.[P-N] from P inner join #TempTbl T on T.MinPN = P.[P-N] and T.[I-T] = P.[I-T]
should give you a single record for each P.[P-N]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply