[RESOLVED] SQL "select distinct"

Hi,

I have a table with 3 columns, name,number,active . I only want to select unique (name, number) where active=false. I tried to accomplish the following with

Code:

---

SELECT DISTINCT name, number
FROM Table1
WHERE active=false

---

However, I feel like my query is only looking for the distinct results between the records where active=false. But I want the distinct to check that the records are unique inside the whole table no matter what the active columns says, and display the distinct results of rows with active=false .

Please let me know what I am missing.


Thanks

So you are saying that when you exclude the where clause you get a listing of distinct records and if you look at the values where active is false its a different group than when you execute the sql statement with the where clause?

Quote:

---

Originally Posted by met0555

Hi,

I have a table with 3 columns, name,number,active . I only want to select unique (name, number) where active=false. I tried to accomplish the following with

Code:

---

SELECT DISTINCT name, number
FROM Table1
WHERE active=false

---

However, I feel like my query is only looking for the distinct results between the records where active=false. But I want the distinct to check that the records are unique inside the whole table no matter what the active columns says, and display the distinct results of rows with active=false .

Please let me know what I am missing.


Thanks

---

huh? wha?
How is that any different?

-tg

@RobDog888 : Yes it is different



Maybe there is problem with my logic and expectation .

ex. Table1

Code:

---

a        b        c
--        --        --
aa        bb        false
aaa        bbb        false
aaaa        bbbb        false
bbbb        aaaa        true
aa        bb        true
aaa        bbb        true
aaaa        bbbb        true

---

If I run the query

Code:

---

SELECT DISTINCT a,b
from Table1
WHERE c=true

---

I assume the result will be

Code:

---

bbbb        aaaa        true
aa        bb        true
aaa        bbb        true
aaaa        bbbb        true

---

My expectation

Code:

---

bbbb        aaaa        true

---

Please let me know if u still have question

Why would that be your expectation? why wouldn't aa bb not show up?

-tg

Hmm I guess, DISTINCT alone wount do the work for me. I Just realized i'm looking for something like this



ex. data

Code:

---

aa        bb        true
aa        bb        false
bb        aa        true
bb        aa        true

---

incase of

Code:

---

aa        bb        true
aa        bb        false

---

If a false is detected then I don't want to see any of that record ( not sure how to accomplish this)



incase of

Code:

---

bb        aa        true
bb        aa        true

---

just want to keep keep one, (can be accomplished with DISTINCT)

Thanks

What database are you using? Access? SQL Server? Oracle? MySQL?

There is a way, but the exact syntax varies...

for SQL Server, you can do it like this:

Code:

---

create table tempDistinctData (
        a varchar(10),
        b varchar(10),
        c bit);

--aa        bb        false
--aaa        bbb        false
--aaaa        bbbb        false
--bbbb        aaaa        true
--aa        bb        true
--aaa        bbb        true
--aaaa        bbbb        true

insert into tempDistinctData (a,b,c) values ('aa','bb', 0)
insert into tempDistinctData (a,b,c) values ('aaa','bbb', 0)
insert into tempDistinctData (a,b,c) values ('aaaa','bbbb', 0)
insert into tempDistinctData (a,b,c) values ('bbbb','aaaa', 1)
insert into tempDistinctData (a,b,c) values ('aa','bb', 1)
insert into tempDistinctData (a,b,c) values ('aaa','bbb', 1)
insert into tempDistinctData (a,b,c) values ('aaaa','bbbb', 1)


select * from tempDistinctData

select a,b
from tempDistinctData
except
select a,b
from tempDistinctData
where c = 0



drop table tempDistinctData;

---

This gave me the result you expected.

In short it will exclude any combination row where there is at least one false row.

But this is a t-sql solution for SQL Server... for other databases there will be variations. You can also use a inner select, right join... just depends.

-tg

I see, unfortunately I'm using MS Access in this case, I will keep looking for the right syntax


thanks

Submitted for your approval, a one-way link to another site, another time all found through the google zone.

http://stackoverflow.com/questions/1...-clause-except

or maybe not... hmmmm...

what you might have to do is a sub-query of somekind.

Code:

---

select distinct a,b
from tempDistinctData
where a + b not in (select a + b from tempDistinctData  where c = false)

---

I'm a tad grossed out by that, but it should work...


this post is a bit old & dated
http://p2p.wrox.com/access/55073-min...ms-access.html
here they suggest using the unmatched query wizard, then taking the SQL it generates and breaking it up into something useful. Not sure if that's an option or not.

-tg

Maybe I'm reading you wrong but I don't think you want distinct at all. What you really want is Having Count(*) = 1 (ie there is only one instance of this combination in the table). That's not the same as distinct which would return you one row for every combination, no matter how many instances of it existed. They sound similar but are actually completely different.

If I've understood you correctly this would work (typed straight in so watch the syntax):-

Code:

---

with cteSingleInstanceRows as
(Select Name, Number
From Table1
Group By Name, Number
Having Count(*) = 1)
Select T1.*
From Table1 T1
Join cteSingleInstanceRows SIR
  on T1.Name = SIR.Name
  and T1.Number = SIR.Number
Where Active = 'true'

---

No, I don't think having a count of 1 works either....
If you look at the third case of pst #6,... there's two rows, both are true, so that distinct combination needs to be returned.

From what I gathered is that no matter how many there are, if there is even one false row, that combination is to be thrown out.


-tg

So i ended up doing it the long way...

I improved my data validation code (vb.net) before insertion to the DB at the application level (to avoid this scenario :

Quote:

---

incase of

Code:

---

aa        bb        true
aa        bb        false

---

If a false is detected then I don't want to see any of that record ( not sure how to accomplish this)

---

And used the SQL distinct query to return unique rows.

next time i will stick with MSSQL than MS ACCESS.

Thanks everyone for your help.