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