Results 1 to 12 of 12

Thread: [RESOLVED] SQL "select distinct"

  1. #1

    Thread Starter
    Frenzied Member met0555's Avatar
    Join Date
    Jul 2006
    Posts
    1,385

    Resolved [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

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    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 PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI 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

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,956

    Re: SQL "select distinct"

    Quote Originally Posted by met0555 View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    Frenzied Member met0555's Avatar
    Join Date
    Jul 2006
    Posts
    1,385

    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
    Code:
    bbbb	aaaa	true

    Please let me know if u still have question

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,956

    Re: SQL "select distinct"

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Frenzied Member met0555's Avatar
    Join Date
    Jul 2006
    Posts
    1,385

    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

  7. #7
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,956

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8

    Thread Starter
    Frenzied Member met0555's Avatar
    Join Date
    Jul 2006
    Posts
    1,385

    Re: SQL "select distinct"

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


    thanks

  9. #9
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,956

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,612

    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'
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,956

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12

    Thread Starter
    Frenzied Member met0555's Avatar
    Join Date
    Jul 2006
    Posts
    1,385

    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
  •  



Click Here to Expand Forum to Full Width