SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select queries not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

540 Posts

Posted - 09/09/2013 :  13:30:58  Show Profile  Reply with Quote
I am using teh below query but with order by clause is throwing an error, how to use it with order by. is it possible.

Select  a.*,
        b.*
 
from    (   
		SELECT  PI.[SysPersonIdentifierSK],
		p.SysPersonId as syspersonID
      ,PI.SysPersonSK
      ,PI.PersonIdTypeRefSK
      ,PI.RowSourceID
      ,PI.PersonIdText
 
  FROM [GBS_Core].[SysPerson].[PersonIdentifier] PI 
  INNER JOIN   [GBS_Core].SysPerson.Person P
  ON PI.SysPersonSK = P.SysPersonSK
  where PI.SysPersonSK = 1
  -- order by  syspersonID , PI.PersonIdText
                       
                        ) a  
INNER join 
              (

select syspersonid,
       personID_name,
       PerosnID_value
       
 from
(
SELECT DISTINCT 
      RTRIM(LTRIM(CAST(18 AS varchar(5)) + CAST(UserID AS varchar(500))))       AS syspersonid,
      CAST (ISNULL(UserGUID,'') AS VARCHAR(100))                                AS UserGUID,
      CAST (ISNULL(AudienceID,'') AS VARCHAR(100))                              AS AudienceID,
      CreateDateTime                                                            AS EffectiveBeginDate, 
      '2100-01-01'                                                              AS EffectiveEndDate,
      JobOperationCode									                        AS OperationCode,
      CASE WHEN JobOperationCode = 1 THEN 0 ELSE 1 END                          AS RowActive,
      CASE WHEN JobOperationCode = 1 THEN 0 ELSE 1 END                          AS CurrentRecordIndicator
FROM   idm.dbo_User) t
unpivot
(PerosnID_value for personID_name in ([UserGUID],[AudienceID])
)as unpvt
where syspersonid = 1810

--order by syspersonid, PerosnID_value                             
 
        ) b

        on   a.syspersonID = b.syspersonID
where 
a.PersonIdText <> b.PerosnID_value


Thank you very much for the helpful info.

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/09/2013 :  13:35:42  Show Profile  Reply with Quote
Use just a single order by clause at the very end of the entire statement.

Be One with the Optimizer
TG
Go to Top of Page

cplusplus
Aged Yak Warrior

540 Posts

Posted - 09/09/2013 :  14:03:10  Show Profile  Reply with Quote
Hello TG, thks.

But teh column names are different in first order by vs second order by.

but the content is same. just the column names are different.

How can i handle one single order by?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/09/2013 :  14:20:29  Show Profile  Reply with Quote
Whatever the column names are in the results is how you should refer to them. Alternatively you could reference their position. ie:
order by 2,5 --second and fifth column in the results.

Be One with the Optimizer
TG
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 09/10/2013 :  00:49:35  Show Profile  Reply with Quote
Try This ,

SELECT TOP(100) a.*,
b.*

FROM (
SELECT TOP(100) PI.[SysPersonIdentifierSK],
p.SysPersonId as syspersonID
,PI.SysPersonSK
,PI.PersonIdTypeRefSK
,PI.RowSourceID
,PI.PersonIdText

FROM [GBS_Core].[SysPerson].[PersonIdentifier] PI
INNER JOIN [GBS_Core].SysPerson.Person P
ON PI.SysPersonSK = P.SysPersonSK
WHERE PI.SysPersonSK = 1
ORDER BY syspersonID , PI.PersonIdText

) a
INNER join
(

SELECT TOP(100) syspersonid,
personID_name,
PerosnID_value

FROM
(
SELECT DISTINCT
RTRIM(LTRIM(CAST(18 AS VARCHAR(5)) + CAST(UserID AS VARCHAR(500)))) AS syspersonid,
CAST (ISNULL(UserGUID,'') AS VARCHAR(100)) AS UserGUID,
CAST (ISNULL(AudienceID,'') AS VARCHAR(100)) AS AudienceID,
CreateDateTime AS EffectiveBeginDate,
'2100-01-01' AS EffectiveEndDate,
JobOperationCode AS OperationCode,
CASE WHEN JobOperationCode = 1 THEN 0 ELSE 1 END AS RowActive,
CASE WHEN JobOperationCode = 1 THEN 0 ELSE 1 END AS CurrentRecordIndicator
FROM idm.dbo_User) t
unpivot
(PerosnID_value FOR personID_name in ([UserGUID],[AudienceID])
)AS unpvt
WHERE syspersonid = 1810

ORDER BY syspersonid, PerosnID_value

) b

ON a.syspersonID = b.syspersonID
WHERE
a.PersonIdText <> b.PerosnID_value


veeranjaneyulu
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 09/11/2013 :  07:43:40  Show Profile  Reply with Quote
That's not even the same query. TOP 100 will only return 100 rows!
AS TG says, you need to put the ORDER BY right outside any of the sub-selects for it to make any sense.
Do the select first then the ORDER BY.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000