SELECT the Duplicate Field

[Gogzilla]

Hi All

I have the data as the below table

Id Topic A Topic B
1 AA A
1 ZZ Z
2 BB C
2 YY D
3 CC H
3 CC V
4 DD G
4 DD Y
5 GG P
5 GG W

But my id field is duplicate, i would like to write the SQL statement for
selecting the data as the following results (order by Alphabet in both of
Topic A and Topic B)

Id Topic A Topic B
1 AA A
2 BB C
3 CC H
4 DD G
5 GG P

How can i write the SQL statement for solving this problem?

Thanks

Gogzilla

 

[AccessVandal via AccessMonster.com]

Try GroupBy.

SELECT [tblTopic].[ID], First([tblTopic].[TopicA]) AS TopicA, First([tblTopic]
..[TopicB]) AS TopicB
FROM tblTopic
GROUP BY [tblTopic].[ID];

 

[John Spencer]

The following might work for you.

SELECT Id, TopicA, TopicB
FROM TableTopics as A
GROUP BY Id, TopicA, TopicB
HAVING Min(TopicA & " " & TopicB)

 

[Gary Walter]

here be the "form" of the SQL I think
you wanted:

(replace "yurtable" w/actual name of table,
and change "Topicx" to "[Topic x]")

SELECT
t.Id,
t.TopicA,
t.TopicB
FROM
yurtable As t
WHERE
(t.TopicA & " " & t.TopicB)=
(SELECT Min(q.TopicA & " " & q.TopicB)
FROM yurtable AS q
WHERE q.Id=t.Id
GROUP BY q.Id)

 

[Gary Walter]

actually, I believe "group by" in subquery
was not needed:

SELECT
t.Id,
t.TopicA,
t.TopicB
FROM tblGogzilla AS t
WHERE
(t.TopicA & " " & t.TopicB)=
(SELECT
Min(q.TopicA & " " & q.TopicB)
FROM tblGogzilla AS q
WHERE q.Id=t.Id)

here be the "form" of the SQL I think
you wanted:

(replace "yurtable" w/actual name of table,
and change "Topicx" to "[Topic x]")

SELECT
t.Id,
t.TopicA,
t.TopicB
FROM
yurtable As t
WHERE
(t.TopicA & " " & t.TopicB)=
(SELECT Min(q.TopicA & " " & q.TopicB)
FROM yurtable AS q
WHERE q.Id=t.Id
GROUP BY q.Id)

I have the data as the below table

Id Topic A Topic B
1 AA A
1 ZZ Z
2 BB C
2 YY D
3 CC H
3 CC V
4 DD G
4 DD Y
5 GG P
5 GG W

But my id field is duplicate, i would like to write the SQL statement for
selecting the data as the following results (order by Alphabet in both of
Topic A and Topic B)

Id Topic A Topic B
1 AA A
2 BB C
3 CC H
4 DD G
5 GG P

How can i write the SQL statement for solving this problem?

Thanks

Gogzilla

 

[Gogzilla]

Hi All

Thanks for all your advices. It works for me in case of the first reply but
the second and third can not solve. It shows this message
"Invalid use of '.' , '!' , or '()'. in query expression 't.TopicB' "

Thanks again

Gogzilla

actually, I believe "group by" in subquery
was not needed:

SELECT
t.Id,
t.TopicA,
t.TopicB
FROM tblGogzilla AS t
WHERE
(t.TopicA & " " & t.TopicB)=
(SELECT
Min(q.TopicA & " " & q.TopicB)
FROM tblGogzilla AS q
WHERE q.Id=t.Id)

here be the "form" of the SQL I think
you wanted:

(replace "yurtable" w/actual name of table,
and change "Topicx" to "[Topic x]")

SELECT
t.Id,
t.TopicA,
t.TopicB
FROM
yurtable As t
WHERE
(t.TopicA & " " & t.TopicB)=
(SELECT Min(q.TopicA & " " & q.TopicB)
FROM yurtable AS q
WHERE q.Id=t.Id
GROUP BY q.Id)

I have the data as the below table

Id Topic A Topic B
1 AA A
1 ZZ Z
2 BB C
2 YY D
3 CC H
3 CC V
4 DD G
4 DD Y
5 GG P
5 GG W

But my id field is duplicate, i would like to write the SQL statement for
selecting the data as the following results (order by Alphabet in both of
Topic A and Topic B)

Id Topic A Topic B
1 AA A
2 BB C
3 CC H
4 DD G
5 GG P

How can i write the SQL statement for solving this problem?

Thanks

Gogzilla