[NBLUG/talk] OT - SQL query question

[Eric Eisenhart]

On Fri, Dec 02, 2005 at 10:12:19AM -0800, Mark Street wrote:
> This is slightly off topic.  I have an old dBase database with 12000 records.  
> There are some records with the same address field.  I need to strip out the 
> DISTINCT address records into a separate database.
> 
> SELECT DISTINCT ADDR1 FROM MYDB gets me the unique addresses but I need the  
> other fields as well.
> 
> What would be the best way to do this?

Errr...  I think I need more definition for the problem...

So, let's say your table looks like this:

---+------+-------+-------+
ID | NAME | ADDR1 | ADDR2 |
---+------+-------+-------+
1  | Bob  | 1234  | St.   |
2  | Sue  | 1234  | St.   |
3  | Sue  | 1234  | Ave.  |
4  | Joe  | 7689  | Wy.   |
---+------+-------+-------+

(obviously there's more to it than that and everything's longer, but it
seems like this covers your basic potential questions...  an "address"
consists of multiple "address" lines, a city, state, maybe a postal code and
maybe a country)

your query, of course, returns: 1234 and 7689 (with no connection to
anything else).  When you say you "need the other fields", what should you
get back in this case?

this:
 
Bob | 1234 | St.
Sue | 1234 | St.
Sue | 1234 | Ave.
Joe | 7689 | Wy.

or this:

1234 | St.
1234 | Ave.
7689 | Wy.

or this:

1234 | St.
7689 | Wy.

????
-- 
Eric Eisenhart
NBLUG Co-Founder, Scribe and InstallFest Coordinator
The North Bay Linux Users Group -- http://nblug.org/
eric at nblug.org, IRC: Freiheit at fn AIM: falschfreiheit