SQL and Returning unique records

15 Dec 2005 6:07 PM

Linda

I have the following table structure:

email                    A                B
a**@acme.com           apples         product1
a**@acme.com           apples         product2
fr***@acme.com         apples         product5
a**@acme.com           apples         product3

And would like to return a unique value:
email                    A                B
a**@acme.com           apples         product1

I am using:

"SELECT DISTINCT email, A, B from tblTransactions" but I am not getting
a unique value for email.

15 Dec 2005 6:12 PM

JI

You won't get a unique list for emails that way. You are selecting a
distinct list of those three columns.

If you want a list of unique emails you will have to do an aggregate on the
other two columns.

i.e.
select email, min(a), min(b)
from tblTransactions
group by email
Show quote

"Linda" <Lindy.***@gmail.com> wrote in message
news:1134670046.074561.84680@g44g2000cwa.googlegroups.com...
>I have the following table structure:
>
> email                    A                B
> a**@acme.com           apples         product1
> a**@acme.com           apples         product2
> fr***@acme.com         apples         product5
> a**@acme.com           apples         product3
>
>
> And would like to return a unique value:
> email                    A                B
> a**@acme.com           apples         product1
>
>
> I am using:
>
> "SELECT DISTINCT email, A, B from tblTransactions" but I am not getting
> a unique value for email.
>

15 Dec 2005 6:18 PM

David Portas

Linda wrote:
Show quote

> I have the following table structure:
>
> email                    A                B
> a**@acme.com           apples         product1
> a**@acme.com           apples         product2
> fr***@acme.com         apples         product5
> a**@acme.com           apples         product3
>
>
> And would like to return a unique value:
> email                    A                B
> a**@acme.com           apples         product1
>
>
> I am using:
>
> "SELECT DISTINCT email, A, B from tblTransactions" but I am not getting
> a unique value for email.

What is/are the key(s) of this table? How do you want to determine
which one row to return?

Don't forget that a table has no inherent logical order. Are you saying
you just want any one random row for each email address? That's a poor
spec in my opinion. Pin down the business requirement to something more
precise. Random results are hard to write and test and end users
normally perceive unpredictable results as a bug.

--
David Portas
SQL Server MVP
--

15 Dec 2005 6:31 PM

Linda

I am looking for the first record with the minimum value in column B.
Not random. Getting one email address is sufficient, so

select email, min(a), min(b)
from tblTransactions
group by email

is good.

15 Dec 2005 6:36 PM

JI

Note: that if you want a distinct list of email and column a then the
following will work for that.

select email,a,min(b)
from tblTransactions
group by email,a
Show quote

"Linda" <Lindy.***@gmail.com> wrote in message
news:1134671479.680874.197400@g43g2000cwa.googlegroups.com...
>I am looking for the first record with the minimum value in column B.
> Not random. Getting one email address is sufficient, so
>
> select email, min(a), min(b)
> from tblTransactions
> group by email
>
> is good.
>