-
Dec 30th, 2014, 12:32 PM
#1
Thread Starter
Frenzied Member
[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
-
Dec 30th, 2014, 12:40 PM
#2
Re: SQL "select distinct"
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?
VB/Office Guru™ (AKA: Gangsta Yoda™ ®)
I dont answer coding questions via PM. Please post a thread in the appropriate forum.
Microsoft MVP 2006-2011
Office Development FAQ (C#, VB.NET, VB 6, VBA)
Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
If a post has helped you then Please Rate it!
• Reps & Rating Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API Viewer utility • .NET API Viewer Utility •
System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6
-
Dec 30th, 2014, 12:42 PM
#3
Re: SQL "select distinct"
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
-
Dec 30th, 2014, 01:22 PM
#4
Thread Starter
Frenzied Member
Re: SQL "select distinct"
@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
Please let me know if u still have question
-
Dec 30th, 2014, 01:47 PM
#5
Re: SQL "select distinct"
Why would that be your expectation? why wouldn't aa bb not show up?
-tg
-
Dec 30th, 2014, 01:56 PM
#6
Thread Starter
Frenzied Member
Re: SQL "select distinct"
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
-
Dec 30th, 2014, 02:12 PM
#7
Re: SQL "select distinct"
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
-
Dec 30th, 2014, 06:50 PM
#8
Thread Starter
Frenzied Member
Re: SQL "select distinct"
I see, unfortunately I'm using MS Access in this case, I will keep looking for the right syntax
thanks
-
Dec 30th, 2014, 07:10 PM
#9
Re: SQL "select distinct"
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
-
Dec 31st, 2014, 05:22 AM
#10
Re: SQL "select distinct"
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'
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 31st, 2014, 09:46 AM
#11
Re: SQL "select distinct"
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
-
Jan 3rd, 2015, 09:05 PM
#12
Thread Starter
Frenzied Member
Re: SQL "select distinct"
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 :
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|