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

Try a GROUP BY instead of ORDER BY. It should still force the sort.

I'd use both GROUP BY and ORDER BY to get what you've described.

-PatP

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

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)

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...

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,

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

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.

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.