By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

Unique Records Question

P: 2
Basically I am trying to do a query that returns records where the count value of a sorted list of a column is equal to 1.

Ex.

Column1 Column2
1 1
7 1
3 2
9 3
5 3


I need a query that only returns '3 2', as for my purposes, the others are duplicates. Ive been trying to use the unique values property, but have been largley unsuccesful so far.

I was thinking of just stripping column 2, and just find the unique values there, and than linking this new table to the old 1 so as to get the needed info from column 1 back. I am curious though if there is an easier way, especially one through the use of intelligent use of criteria.

Any help would be greatly appreciated.
Aug 14 '06 #1
Share this Question
Share on Google+
4 Replies


100+
P: 179
Be more specific dude.

comteck
Aug 24 '06 #2

P: 2
Check out the next 2 columns
(imagine the space marks another cell/field)

1 1
1 2
1 3
1 4
1 5
2 1
2 3
2 5
2 7
3 1
3 5
3 8
3 9


If I sort by the 2nd column I get:

1 1
2 1
3 1
1 2
1 3
2 3
1 4
1 5
2 5
3 5
2 7
3 8
3 9


As can be seen, in the second column there is ONLY 1 occurence of the numbers (2, 4, 7, 8, 9) I want these rows, and all their attacthed information (which in practice include about 1-5 more columns.)

I just want to add, I already found a complex way of doing this, that involves 2-3 different queries, (depending on how you count an append query) and the following criteria:
In (SELECT [Column_2] FROM [Table_2] As Tmp GROUP BY [Column_2] HAVING Count(*)=1 )

after doing a query that all that it does, is display Unique values (Distinct function in SQL).

Cant get them into 1 query, was curious if it was possible at all.
Aug 24 '06 #3

100+
P: 179
Does it have to be a query. It might be easier to do in VB.

comteck
Aug 24 '06 #4

PEB
Expert 100+
P: 1,418
PEB
The ways that I see ARE

Imagine your table is Table with col1 and col2
1. Using only one query:

SELECT *
FROM Table
WHERE (col2=(SELECT col2 FROM Table GROUB BY col2 WHERE (Count(col2)=1))=1);

2. Using 2 queries:

First you create:
Select col2 FROM Table GROUP BY col2 WHERE Count(col2)=1;

Then

Query with table Table and the previous query, you do the join between col2 and col2 and so you have all rows from Table that are unique!
Aug 31 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.