Selecting distinct records from results

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)

Selecting distinct records from results

Author

Topic

ddcg
Starting Member

2 Posts

Posted - 2004-01-04 : 00:53:05

You SQL Gurus will probably eat this one up, but here is the query that is getting 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. I'm imagining that I may need to put the above query in the stored proc into a temporary table first and then extract the results from that. So where I am getting 2 or more records for each unique P-NA value today, I want to just extract the first result (as this really produces duplicates in what I'm trying to do) Any help would be appreciated.

Thanks!

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-04 : 01:44:30

Records have no inherent order in SQL, so 'first' needs to be clarified, as in: most recent, or smallest value of P.D, or something.

Once 'first' is defined, the recordset is usually generated with a GROUP BY like this:

SELECT P-NA, MAX(D) as MaxD
FROM P
GROUP BY P_NA

Then you pick the complete row by doing an inner join , or WHERE condition using the above recordset
HTH,

Sam

ddcg
Starting Member

2 Posts

Posted - 2004-01-06 : 01:19:39

Still kind of stumped here. I'm still not seeing how you're generating the unique records... i.e. we only want to return one record where the P-NA = spot and one record where the P-NA = dot and one record where the P-NA = spike. Keep in mind that there are duplicates for each P-NA so 5 individual records could have the P-NA = spot and 2 indinvidual records could have the P-NA = dot and 3 individual records could have the P-NA = spike. So While the original query that I have above is returning 10 records today I only want it to result in 3.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-06 : 04:40:11

Supplying sample data and matching expected results will help move this along....

Also...as Sam says....your requirement of what "FIRST means to you" has to be overcome before this can be moved on...

Unless you have an ORDER BY section in your SELECT statement...you cannot GUARANTEE the order of your results...and thus cannot reliably base any subsequent processing on that order...and the solution to your problem lies in having a predictable ORDER on your results.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-06 : 10:26:21

Yes, better to use an temp table. Like this:

select a=identity(int), * into #t from
(
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))
) z

select * from #t where a=(select min(a) from #t tt where tt.P_NA=#t.P_NA)

drop table #t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources