SQL order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL order by

Author

Topic

paruchuris
Starting Member

2 Posts

Posted - 2009-02-16 : 00:55:39

SELECT ACTYHDRID,
ACTIVITYHEADERNAME,
ACTIVITYFULLNM,
ACTIVITYNM,
MAX(FULLNM) AS FULLNM,
Description,
ACTIVITYDSC,
MAX(ADJENDDTM) AS ADJENDDTM,
MAX(ADJSTARTDTM) AS ADJSTARTDTM,
LABORLEV4DSC,
AVG(STDHOURSQTY) AS STDHOURSQTY,
SUM(HOURSQTY) AS ALLOCATEDHRSQTY,
MAX(REPCOMPLETEDQTY) AS REPCOMPLETEDQTY,
MAX(REPSCRAPPEDQTY) AS REPSCRAPPEDQTY,
WFADEFAULT1TXT,
WFADEFAULT2TXT,
MIN(Comments) AS Comments
FROM (SELECT ACTYHDRID,
ACTIVITYHEADERNAME,ACTIVITYNM,ACTIVITYFULLNM,FULLNM,
ACTIVITYDSC,
coalesce(ADJSTARTDTM, ACTSTARTDTM) ADJSTARTDTM,
coalesce(ADJENDDTM, ACTENDDTM) ADJENDDTM,
LABORLEV4DSC,
STDHOURSQTY,
VP_WFAREPACTYEVENT.HOURSQTY,
REPCOMPLETEDQTY,
REPSCRAPPEDQTY,
WFADEFAULT1TXT,
WFADEFAULT2TXT,
(SELECT Description AS Description FROM natjobops NT WHERE NT.job = VP_WFAREPACTYEVENT.ACTIVITYHEADERNAME and operation =VP_WFAREPACTYEVENT.ACTIVITYNM ) AS Description,
ISNULL( (SELECT isnull(commenttypedesc,'Add') AS Comments FROM commenttype WHERE commenttypeid = ce.commenttypeid),'Add') AS Comments,
commententry
FROM VP_WFAREPACTYEVENT
INNER JOIN WFAREPLABACCT AS wrla ON REPACTYSPANID = wrla.WFAREPACTYSPANID
INNER JOIN LABORACCT AS la ON wrla.LABORACCTID = la.LABORACCTID
INNER JOIN (SELECT DISTINCT ACTYHDRID HDRID
FROM VP_WFAREPACTYEVENT
WHERE (ADJENDDTM >= '1/11/2009 12:00AM')
AND (ADJENDDTM < '1/24/2009 12:00PM')) V2 ON ACTYHDRID = V2.HDRID
LEFT OUTER JOIN CommentEntry AS CE ON dbo.VP_WFAREPACTYEVENT.ACTYHDRID = CE.WFAACTYHDRID
WHERE (DIRECTSW = 1)
--AND (LABORLEV4DSC = 'Combustion Assembly')
--AND ((la.LABORLEV4NM IN (@Dept)) OR (la.LABORLEV4NM IS NULL))
AND (la.LABORLEV4NM IN ('0026'))
AND (COMPLSTATTYPEID IN (0,1,2,3))
AND (HELDSTATTYPEID IN (0, 1, 2))
AND (STDQTY IS NOT NULL)
AND (STDHOURSQTY IS NOT NULL)
--AND ACTIVITYID = 168411
AND (ACTIVITYHEADERNAME = '682861')
ORDER BY ADJSTARTDTM asc
) AS a
--WHERE (Comments IN (@CommentType)) OR (Comments IS NULL)

--AND FULLNM = @Person
GROUP BY ACTYHDRID,ACTIVITYHEADERNAME,ACTIVITYFULLNM,ACTIVITYNM, ACTIVITYDSC, LABORLEV4DSC,WFADEFAULT1TXT,WFADEFAULT2TXT, Description
ORDER BY ACTIVITYHEADERNAME, ACTIVITYFULLNM

Hi All,

I am trying to execute the above query, I am using this query to union with othr query, I am gettng proble when I am executing it because order by is working.
Could you please have any suggestions for me?

Regards,
SP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-16 : 01:14:42

You haven't explained yourself clearly. Are you getting an error? Is it just not sorting it properly?

Show us some sample data to illustrate your issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

heavymind
Posting Yak Master

115 Posts

Posted - 2009-02-16 : 05:02:50

you can't union ordered queries. though you can enclouse your unions into a subquery and order the resulting query.

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 05:59:39

if you need to use order by in any of select batch you need to use top x percent and then wrap it in a derived table .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources