Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Unanswered: Removing dupes (distinct like)

    Greets, prob. simple problem for experts but my sql is at it's infancy

    select Entity.OID from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW,Entity.OID

    Gives me:
    5266
    5266
    5266
    5088
    5088

    etc, but I want only unique entity oids returned:
    5266
    5088

    How I can achieve this w/ SQL as distinct no workie bcause of order by?

    Thanks
    --PO

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Try a GROUP BY instead of ORDER BY. It should still force the sort.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use both GROUP BY and ORDER BY to get what you've described.

    -PatP

  4. #4
    Join Date
    Jun 2004
    Posts
    8

    No workie

    select Entity.OID from Entity
    ,EN,ENXP as ENXP0

    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    group by ENXP0.ST_NAMEROW,Entity.OID
    order by ENXP0.ST_NAMEROW,Entity.OID

    (and other combinations) seems still produce exact same dupe OID's.

    group by Entity.OID
    order by ENXP0.ST_NAMEROW,Entity.OID

    won't work (on SQL Server 2000 atleast as ENXP0.ST_NAMEROW must be included also on group by)

    Any other suggestions?

    --PO

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    Did you try a subselect?

    select distinct Entity.OID from
    (select Entity.OID from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW,Entity.OID)

  6. #6
    Join Date
    Jun 2004
    Posts
    8
    Yepp, unfortunately SQL server does not allow order by's at subselects (otherwise prob could be solved easily

    Server: Msg 1033, Level 15, State 1, Line 10
    The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.



    TOP stuff is not really applicable cause I must fetch all rows possible and seems that SQL server does not understand it inside inline select.

    Damn, this *looks* like piece of cake to solve...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "distinct no workie bcause of order by" mean?

    of course you can have DISTINCT and ORDER BY

    however, since you are returning only one column, it seems rather unnecessary

    rule of thumb: select what you want to order

    if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

    so, what was your question again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Posts
    8
    Quote Originally Posted by r937
    what does "distinct no workie bcause of order by" mean?

    of course you can have DISTINCT and ORDER BY

    however, since you are returning only one column, it seems rather unnecessary

    rule of thumb: select what you want to order

    if you want to order your results by order by ENXP0.ST_NAMEROW and Entity.OID, then you should select both of these columns

    so, what was your question again?
    "Distinct no workie" means that for example this won't ofcourse work:

    select distinct Entity.OID from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW

    Server: Msg 145, Level 15, State 1, Line 1
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Anyway we need those OID's and *only unique* OID's ordered by ENXP0.ST_NAMEROW. OID's are later used on another Query to fetch other rows from db (on that particular order made by ENXP0.ST_NAMEROW). Reason is that our system is using SQL generator to generate SQL clauses and reason for that is that writing SQL by hand is far too slow and difficult todo against schema we are using.

    Thanks,

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think you're missing the point

    here are a bunch of OIDs --

    5266
    5088
    5937
    5013

    how can you tell what their ENXP0.ST_NAMEROW values are? you can't

    therefore how would you know if they were, or were not, in ENXP0.ST_NAMEROW order? you can't

    you simply cannot order OID values by ENXP0.ST_NAMEROW
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2004
    Posts
    8
    For example:

    select ENXP.ST_NAMEROW from ENXP
    where ENXP.TOID in (5266)

    (as we have duplicate main oid on ENXP table)

    and if we can get actual OID's ordered by ENXP.ST_NAMEROW using query like:

    select Entity.OID from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW,Entity.OID

    which orders them by ENXP0.ST_NAMEROW so do we have OID's ordered by NAMEROW? I think so, but with alot of duplicate values because name is formed using multiple ENXP0.ST_NAMEROW's.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, good luck to you sir, i do not understand what you're doing and i cannot help you further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2004
    Posts
    8
    Quote Originally Posted by r937
    well, good luck to you sir, i do not understand what you're doing and i cannot help you further
    Yep, thanks.
    Well basic question was that is there way using SQL to remove duplicate OID's from resultset what this query return (and it must return only OID's ordered by ENXP0.ST_NAMEROW) :

    select Entity.OID from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW

    Result:
    5266
    5266
    5266

    Can be done ofcourse on server side as last resort if cannot be done using SQL.

    Query like:
    select distinct Entity.OID,ENXP0.ST_NAMEROW from Entity
    ,EN,ENXP as ENXP0
    where Entity.OID=EN.TOID
    and EN.CS_ST_USE='L'
    and EN.OID=ENXP0.POID
    and UPPER (ENXP0.ST_NAMEROW) LIKE UPPER ('P%')
    and ENXP0.CS_ST_TYPE='GIV'
    and ENXP0.CS_ST_QUALIFIER=null
    order by ENXP0.ST_NAMEROW,Entity.OID

    Will return for example:
    5266 P
    5266 Pamela
    5266 Prognost

    But again we need only those unique OID's returned, no other data at this point.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •