need help to filter records in SQL

[leeland]

Hello

I have a huge SQL view I am writing at work and I have come across a problem as far as filtering records. I have a record set that gets returned but there are duplicate records.

for instance

Rec URN Name sequence #

123123 bob 1

123123 bob 2


Now everything in the records is identical except for one field, so it is getting counted twice when it should only get counted once.

In the SELECT part I have some aggerate functions so I have to use the GROUP BY command for the rest of fields that are not aggerate.

There has to be a way to only select one record based on the URN vs both of them


I have tried the disctinct command and that didn't work within the SELECT statement.

Any ideas would be helpful

Thanks

Leeland

 

[KLin]

Take out the sequence# field out of your query/view and all should be well in the land of SQL.

 

[leeland]

well that is the problem I know it woudl be that easy to just take that out but I can't unfortunately


I did get it to work but I think I did it a jimmy rigged way


I had to put Max( fieldname) around every field in the select statement


Then in the GROUP BY

the only field I used was the Rec_urn

That grouped the duplicate fields together and only counted one record. I don't think that is the right way to do this but it works

 

[Schrodinger]

CREATE OR REPLACE TABLE 2ndtablename
AS
[...your select...]


SELECT DISTINCT * FROM 2ndtablename



ROFL very gross indeed

I'm no database person so there is probably a much cleaner way of doing it (this way is slow because two selects take place)

 

[royaldank]

Can you do a select distinct URN on the SQL statement you have now?

Ex. x = your current SQL statement.

select distinct URN from (x)

 

[leeland]

Originally posted by: royaldank
Can you do a select distinct URN on the SQL statement you have now?

Ex. x = your current SQL statement.

select distinct URN from (x)

I tried that and for some reason it didn't work

here is what I had

select

distinct(rec_urn)
xxx
xxx
xx

FROM
table

WHERE
criteria

and it still gave me the duplicates



The only way I got it work was this way


SELECT

rec_urn
Max(xxx)
Max(xxx)


FROM
table

WHERE
criteria

GROUP BY

rec_urn



that way it grouped on the REC_URN and did not give me the duplicates...I still don't fully understand why the DISTINCT function didn't work

I think it might be because the it looks at the record (or row) as a whole vs just the column of REC_URN

is this a wrong way to look at it ?

so if the records were like what I posted above


123 microsoft version 1

123 microsoft version 2


it would see that each record is actually disctinct within the list and still include them....

I have to ask some people at work about it still to see if they can give me a decent answer

thanks for the info guys


Leeland