Disctinct selections

Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column

for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

AlanHi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

Alan

is there a way to tell which one of the records from thr above example should be displayed ... or should it be a random one.|||It really doesn't matter which one of the records gets selected. I just need to end up with one.

Is there a nth funtion in SQL perhaps?

Alan|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname|||no, there is no "nth" function

i should also like to take this opportunity to point out that DISTINCT is not a function either
select lastname
, min(firstname) as lowest_firstname_for_this_lastname
, max(address) as highest_address_for_this_lastname
, avg(zipcode) as average_zip_assuming_its_numeric
from daTable
group
by lastname

Ok, this could work but will it return my actual record. For my example I've excluded other fields that I need to select from my table such as Individual number, family number, gender and much more.

I think my original example was not the best

Your query if used with the MIN aggregated function won't I end up with the lowest value first name, lowest value gender etc..

Won't I ended up with the lowest value first name, lowest value gender, and lowest value induvidual number. etc?

So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?|||So Alan Andrews might end up with gender code of F and the wrong customer number, etc.?that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?|||that is quite correct

it indicates that what you are doing (combining multiple rows for the same last name) is probably the wrong approach, as it will likely mash up different people

if the same lastname occurs on multiple rows, do you have any way of differentiating the various rows? any rule for which one you want? and please don't say again "oh, any one"

what is the primary key of your table?

I have a Unique Site number that is unique to each family. I will probably be doing the group by this number instead of the last name.

How about the 1st record in a group?

I tried using FIRST, but it doesn't appear to be an aggregated function in SQL server 2005.|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?|||there is no such concept as "first" because rows don't have a position

and using Unique Site number merely shifts the problem from lastname, it does not make the problem go away

you will still need to somehow specify which row you want from the group of rows which all have the same Unique Site number

the best way to do this is to designate which row based on its primary key, since primary keys are unique

what is the primary key of your table?

I currently don't have a primary key, but I can add one. Once I create this ID field, how would I designate it on my query?

Thanks for all the help!|||I know cursors are forbidden or banished to the WASTELAND:shocked: , and am try to find a way to do this without cursor, sure its possible!

:cool: Till than here is the cursor solution:
(tweeked the employee table a bit)

1> run next 11 line as a batch
declare @.tempname varchar(20)
declare rpt cursor for select distinct lastname from employees
open rpt
fetch next from rpt into @.tempname
while @.@.fetch_status=0
begin
select top 1 * from employees where lastname = @.tempname
fetch next from rpt into @.tempname
end
close rpt
deallocate rpt

Take r937 suggestion, using primary key is better any day, besides two people with same surname can reside a two totally different locations.

:angel: Hope it works, have fun|||Once I create this ID field, how would I designate it on my query?with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname|||Abritrary data is garbage, so why use it at all...

Can you explain to us what you are trying to do?

Bret, I am trying to net my results to one per site number. unsing a select query.

Thanks|||r937 you just gave me an idea:beer: .
This is really weird, the following sql command works:

select * from employees as A
where firstname=(select min(firstname)
from employees as B
where A.lastname = B.lastname)

Even though i have three persons with the same firstname and it returns unique records. does it work for anybody else!!! (i tweaked the employee table)|||with a correlated subquery, which has the same effect as groupingselect lastname
, firstname
, address
, zipcode
, newPKcolumn
from daTable as T
where newPKcolumn
= ( select min(newPKcolumn)
from daTable
where lastname = T.lastname )the effect of the correlated subquery is to chose the (single) row which has the lowest newPKcolumn value from amongst all the rows with the same lastname

This worked great. Thanks for your help!|||Not bad eh, for a guy who isn't a dba|||sql skills are not restricted to DBAs, man

that's like saying "wow, you can speak english -- not bad for a guy who isn't a DBA"

being a DBA means you do stuff like replication, installation, permissions, tuning, administration, etc.

you don't have to know any of that cr@.p to be really good at sql :)|||all the stuff I hate to do...especially on DB2 OS/390

Disctinct Query Help

Can anyone tell me how to write this query correctly... Right now it is returning all of the results instead of just the results with unique emails...

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE email IN (SELECT DISTINCT email FROM Results)

Please help.

Your query is doing exactly what it was written to do. Post some sample data from the table and the expected output so someone here can help fix your query.

|||

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

|||

I am going toguess that you want to get a resultset where the COUNT(*) of each email is 1. How this would look:

SELECT r.ID, r.firstname, r.address1, r.lastname, r.address2, r.city, r.province, r.postalCode, r.phoneNumber, r.email, r.referral, r.cpauseInfo, r.yukonInfo, r.dateAddedFROM Results rWHEREEXISTS (select 1from Results rxwhere rx.email = r.emailgroup by rx.emailhavingcount(*) = 1 )

|||

sheldonj:

This table has entries we represent contest submissions. The form wasn't built very well and it allowed users to submit results numerous times. I want to write a query that will filter the data based on email address and phone numbers. So any records that have the same email address should be deleted. Same goes for phone number.

Should I be creating a temporary table or something?

You don't need a temp table. You are attempting to clean duplicated data. Let me refer you to an article I wrote several years agohere. Here is a sample that could remove duplicates by email address.

/* 1. Query heading */DELETEFROM/* 2. Table with duplicates */ ResultsWHEREEXISTS (SELECT 1FROM/* 3. Table with duplicates, with an alias */ Results bWHERE/* 4. Join each field with *itself*. These are fields that could be Primary Keys */ b. = Results.GROUP BY/* 5. I must GROUP BY these fields because of the HAVING clause and because these are the possible PK */ b.HAVING/* 6. This is the determining factor. We can control our output from here. In this case, we want to pick records where dateAdded is greater than the MIN dateAdded */ Results.[dateAdded] >MIN(b.[dateAdded]) )

|||

Thank you David that was exactly what I was looking for

|||

I think that simplest way to do this is:

SELECT ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAdded
FROM Results
WHERE ID IN (SELECT max(ID)
FROM Results
group by email)

it will return you only single email entries from you table.
If you would like to keep only single record with unique email in your table run

DELETE FROM Results
WHERE not ID IN (SELECT max(ID)
FROM Results
group by email)
Good luck