Getting Unique records...DISTINCT I guess... 
Author Message
 Getting Unique records...DISTINCT I guess...
Hello, all.  I have the following table, and only want the unique or
distinct values for the ID column.  I suppose I'll need the DISTINCT clause.

1234,Camping Site A
3239,Camping  Site B
3043,Camping Site C
1234,Camping Si                        'intentionally typed

I only want returned:
1234,Camping Site A
3239,Camping  Site B
3043,Camping Site C



Sat, 22 Nov 2003 23:23:32 GMT
 Getting Unique records...DISTINCT I guess...

How will you decide which value you want?

1234,Camping Site A

or

1234,Camping Si

?


Quote:
> Hello, all.  I have the following table, and only want the unique or
> distinct values for the ID column.  I suppose I'll need the DISTINCT
clause.

> 1234,Camping Site A
> 3239,Camping  Site B
> 3043,Camping Site C
> 1234,Camping Si                        'intentionally typed

> I only want returned:
> 1234,Camping Site A
> 3239,Camping  Site B
> 3043,Camping Site C



Sat, 22 Nov 2003 23:36:32 GMT
 Getting Unique records...DISTINCT I guess...
Roswell,

This will work for the sample data you posted ... whether it will work for
your actual data, I don't know.

create table Roswell
(
 ID int NOT NULL,
 Data varchar (15)
)
go

insert into Roswell values (1234, 'Camping Site A')
insert into Roswell values (3239, 'Camping  Site B')
insert into Roswell values (3043, 'Camping Site C')
insert into Roswell values (1234, 'Camping Si')

select ID, MAX (Data) as Data
from Roswell
group by ID

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hello, all.  I have the following table, and only want the unique or
> distinct values for the ID column.  I suppose I'll need the DISTINCT
clause.

> 1234,Camping Site A
> 3239,Camping  Site B
> 3043,Camping Site C
> 1234,Camping Si                        'intentionally typed

> I only want returned:
> 1234,Camping Site A
> 3239,Camping  Site B
> 3043,Camping Site C



Sun, 23 Nov 2003 09:15:53 GMT
 Getting Unique records...DISTINCT I guess...
Why aren't you scrubbing the data a little bit in the front end before you send it to the database?  

Thanks to the fact that you did not post any DDL, we have no idea what "id" is identifying, or even if it is an identifer at all, or even the name of this table!  If it was an identifier, then it would be either a PRIMARY KEY or NOT NULL UNIQUE in the DDL and you could not insert the duplicate key.

If you are working from specs this bad for money, update your resume and find a better job.  

--CELKO--

SQL guru at Trilogy
===========================
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 23 Nov 2003 09:59:36 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. getting distinct recordset back in a record which includes a text field

2. Insert record -> getting unique id value

3. Getting one unique updateable record

4. Q: Getting unique record IDs without committing?

5. Getting new Unique Identifier of record in recordset with VC - ADO

6. Getting Unique Record and showing Multiples

7. Search on unique value, return multiple unique records

8. How do I get the FIRST record rather than DISTINCT records

9. DISTINCT vs UNIQUE

10. Selecting distinct columns with a unique key

11. Unique and Distinct

12. Unique or Distinct field in a Report


 
Powered by phpBB® Forum Software