Select a subset of data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select a subset of data

Author

Topic

sca1
Starting Member

9 Posts

Posted - 2007-02-13 : 16:42:21

How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.

Example

Table Name : Sales Table
Field Name : Name Address Phone Zip Sale
Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99
Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34
Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56
Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89
Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52

How would I be able to return the columns Name Address and Phone based on the distinct of Name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 18:01:13

[code]
select *
from {Sales Table] s
inner join
(
select distinct Name
from [Sales Table]
) d
on s.Name = d.Name
[/code]

sca1
Starting Member

9 Posts

Posted - 2007-02-14 : 10:10:09

This Statment did NOT work in returned all of the data in the table. I need to be able to get just one record for each of the distinct data elements in the given field.

quote:

Originally posted by khtan


select *
from   {Sales Table] s
       inner join
       (
           select distinct Name
           from   [Sales Table]
       ) d
       on s.Name = d.Name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 10:26:35

do you have a primary key in the table ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 10:27:48

Tell us which address you want for duplicate records!
First or last alphabetically? Highest/Lowest sale?

We can't guess...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources