Help with Query

[Masterpop]

Hi,

I have the following table:

Column 1 Column 2
A A
A B
A C
B F
B F
C G
C A

I need a query to return the following:
A null
B F
C null

How can I do that?

Thanks,
ALex

[David Portas]

Re: Help with Query

Why did you return an "F" for one row and NULL for the others? I'm
guessing that you only want to return a value in the second column
where that value is the same for every instance of the value in the
first column. I'm also guessing that both columns are not nullable in
your table (because you didn't specify any other columns that could be
a key). If that's correct, try this:

SELECT col1,
CASE WHEN MIN(col2)=MAX(c ol2) THEN MIN(col2) END AS col2
FROM your_table
GROUP BY col1;

(untested)

--
David Portas
SQL Server MVP
--

[--CELKO--]

Re: Help with Query

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

After that ABSOLUTELY BASIC PIECE OF NETIQUETTE, did you read what you
posted? What the @!$% specs for this?? Wait, wait, let me read your
mind and guess that you want to see only those rows whose col_1 groups
have one and only one value for col_2 !!

SELECT F1.col1, F2.col2
FROM Foobar AS F1
LEFT OUTER JOIN
(SELECT col1, MIN(col2)
FROM Foobar
GROUP BY col1
HAVING MIN(col2) = MAX(col2))
AS F2 (col1, col2)
ON F1.col1 = F2.col1;

If you plan to stay in this trade, please learn minimal Netiquette and
how to write a minimal specification.

[Erland Sommarskog]

Re: Help with Query

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue]
> After that ABSOLUTELY BASIC PIECE OF NETIQUETTE, did you read what you
> posted? What the @!$% specs for this?? Wait, wait, let me read your
> mind and guess that you want to see only those rows whose col_1 groups
> have one and only one value for col_2 !!
>...
> If you plan to stay in this trade, please learn minimal Netiquette and
> how to write a minimal specification.[/color]

No, this is not what minimal nettiquette is about. Although, including
CREATE TABLE is nice, it's mainly a good idea, because it helps to
get a good answer.

But minimal and basic nettiquette is about something else: that is about
being polite and friendly. Something you are miserable poor in.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp