|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
April 27th, 2004, 10:51 AM
|
|||
|
|||
How to Retrieve first unique record
Refering to the SQL code below, I need it to return only the first record for each CO_CUSTOMER_CODE. I need to retrieve all of the other fields even if there are duplicates. Right now I get records returned like this:
12345 John Smith URL 12345 Jill Brown URL 12346 Fred Flinstone URL And I want this: 12345 John Smith URL 12346 Fred Flinstone URL Is there a way to do this in SQL Server? SELECT DISTINCT CONTACTS.CO_CUSTOMER_CODE As [Customer #], CONTACTS.CO_CONTACT_NAME As [Name], CONTACTS.CO_EMAIL As [Email] FROM CONTACTS ORDER BY CONTACTS.CO_CUSTOMER_CODE
|
|
#2
April 27th, 2004, 11:52 AM
|
|||
|
|||
|
Quote:
There is no concept of first in a RDBMS. You can only distinguish between records by the values in the different columns in the record. If you wish to retrieve the record that was entered in the database at the earliest point of time, you need to store the date and time when the post was entered. You can also get the record with max/min value (according to the collating sequence) of the email adress for each customer code. Code:
SELECT CONTACTS.CO_CUSTOMER_CODE As [Customer #], CONTACTS.CO_CONTACT_NAME As Name, CONTACTS.CO_EMAIL As Email FROM CONTACTS as c where email in (select max(email) from contacts where co_customer_code = c.co_customer_code) ORDER BY CONTACTS.CO_CUSTOMER_CODE
|
|
#3
April 27th, 2004, 01:06 PM
|
|||
|
|||
|
What I'm trying to do is get back a 'distinct' contact for each customer record from the Contacts table. It doesn't matter which contact, but I want just one instead of multiple contacts returned. I am making a report to show the contact information for customers. If it helps the primary key is
CONTACTS.CO_CONTACT_CODE, CONTACTS.CO_CUSTOMER_CODE
|
|
#4
April 27th, 2004, 05:54 PM
|
|||
|
|||
|
What DBMS?
give this a shot... Code:
select *
from Contacts C
join (select distinct co_customer_code,
max(email) as email,
count(email)
from Contacts
group by co_customer_code
having count(email) > 1) as A
on C.co_customer_code = A.co_customer_code
and C.email = A.email
..all though I'm curious why you need the code and name as the primary key? If you just used the code as the primary key this post would turn irrelevant...each code would then have it's own distinct contact...just curious. EDIT: boogie's query looks like it would work as well. Last edited by Username=NULL : April 27th, 2004 at 06:07 PM. Reason: just looked @ boogie's query...
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > How to Retrieve first unique record |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
