SQL Distinct

[DDhillon]

I want to select distinct value from one field of the table and select rest of
the corresponding fields accordingly. I have a table that has fields:
Fname, Lname, Address, Postal, Phone

I want to select all the records with distinct address field. I tried select
distinct(address), fname, lname, postal, phone from tblmaster. It didnt work:(

I want distinctness only based upon the address field. Rest of the fields
should populate accordingly.

Thank you

[mxstu]

I want distinctness only based upon the address field. Rest of the fields
should populate accordingly.
I'm not sure what you mean by "distinctness Based on the address field". Does
the table contain more than one row with the same: Fname, Lname, Address,
Postal, Phone? For example

[Record 1] Jack, Spry, 80 Sycamore Street, 01234, 987654321
[Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321

If so, you could use a group by clause:

-- not tested
SELECT fname, lname, postal, phone, count(*)
FROM tblmaster
GROUP BY fname, lname, postal, phone

[DDhillon]

Does the table contain more than one row with the same: Fname, Lname, Address,
Postal, Phone?

Table does contain more than one row with same address. I want all the records
with distinct address. Say if I have 5 records in table two of them have same
address. So I have 4 records with distinct address and I want to populate all 4
of them with their respective fields.

Davinder

[DDhillon]

Does the table contain more than one row with the same: Fname, Lname, Address,
Postal, Phone?

Table does contain more than one row with same address. I want all the records
with distinct address. Say if I have 5 records in table two of them have same
address. So I have 4 records with distinct address and I want to populate all 4
of them with their respective fields.

Davinder

[mxstu]

If I'm understanding you correctly, if your table contains these two records

[Record 1] Jack, Spry, 80 Sycamore Street, 01234, 987654321
[Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321
[Record 3] Alan, Muller, 80 Sycamore Street, 01234, 987654321

The results you want are just:

Jack, Spry, 80 Sycamore Street, 01234, 987654321
Alan, Muller, 80 Sycamore Street, 01234, 987654321


In which case, the SELECT statement I posted should do that.

[DDhillon]

SELECT fname, lname, postal, phone, count(*)
FROM tblmaster
GROUP BY fname, lname, postal, phone

This sql statement doesnt contain records from address field. I want to
populate the results with address as well.


[Record 1] Dave, Spry, 80 Sycamore Street, 2233, 983454321
[Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321
[Record 3] Alan, Muller, 34 58th Avenue, 01234, 985654321
[Record 4] John, Miller, 1178 154 Street, 01234, 985654321
[Record 5] Alan, Muller, 3456 Mc Bride Blvd, 01234, 985654321

The results I want is:

Dave, Spry, 80 Sycamore Street, 2233, 983454321
Alan, Muller, 34 58th Avenue, 01234, 985654321
John, Miller, 1178 154 Street, 01234, 985654321
Alan, Muller, 3456 Mc Bride Blvd, 01234, 985654321

I hope I m clear now.

[mxstu]

Apologies for the confusion. I accidentally omitted the address column when
posting the SQL statement. It should of course be:

SELECT fname, lname, Address, postal, phone, count(*)
FROM tblmaster
GROUP BY fname, lname, Address, postal, phone

Is this a typo:

[Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321

[Brian Hogue]

Distinct does not apply to one column. Distinct applies to the set of
columns in the query. Most people fall into this trap. If you want the
unique ADDRESSes then you need to start with a Group By Address. If add
another column to the Group By then uniqueness is determined by the TWO
columns. It sounds like you want to take the answer set from that first
query (Group By Address) and join it back to the base table to get the
corresponding data ... you may need to work around those Addresses that have
multiple rows. hth

-brian

"DDhillon" <webforumsuser@macromedia.com> wrote in message
news:dc0rho$jhh$1@forums.macromedia.com...

> SELECT fname, lname, postal, phone, count(*)
> FROM tblmaster
> GROUP BY fname, lname, postal, phone
>
> This sql statement doesnt contain records from address field. I want to
> populate the results with address as well.
>
>
> [Record 1] Dave, Spry, 80 Sycamore Street, 2233, 983454321
> [Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321
> [Record 3] Alan, Muller, 34 58th Avenue, 01234, 985654321
> [Record 4] John, Miller, 1178 154 Street, 01234, 985654321
> [Record 5] Alan, Muller, 3456 Mc Bride Blvd, 01234, 985654321
>
> The results I want is:
>
> Dave, Spry, 80 Sycamore Street, 2233, 983454321
> Alan, Muller, 34 58th Avenue, 01234, 985654321
> John, Miller, 1178 154 Street, 01234, 985654321
> Alan, Muller, 3456 Mc Bride Blvd, 01234, 985654321
>
> I hope I m clear now.
>
>
>
>

[mxstu]

Originally posted by: DDhillon
[Record 1] Dave, Spry, 80 Sycamore Street, 2233, 983454321
[Record 2] Jack, Spry, 80 Sycamore Street, 01234, 987654321
....
The results I want is:

[Record 1] Dave, Spry, 80 Sycamore Street, 2233, 983454321
....


I think I see the problem now. I was thinking of DISTINCT in the strict sense
(as Brian described it).

So if an address has more than one record associated with it, you only want to
include the first one, right? One method is to JOIN with an aggregate
sub-query that returns only one record per address:

-- assumes table has a unique numeric id column
SELECT m.fname, m.lname, m.postal, m.phone, m.address
FROM tblmaster m INNER JOIN
( SELECT Address, MIN(numericIDColumn) AS numericIDColumn
FROM tblmaster
GROUP BY Address
) AS d ON m.numericIDColumn = d.numericIDColumn