Select queries not working - 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 2008 Forums

Transact-SQL (2008)

Select queries not working

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-09-09 : 13:30:58

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-09 : 13:35:42

Use just a single order by clause at the very end of the entire statement.

Be One with the Optimizer
TG

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-09-09 : 14:03:10

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?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-09 : 14:20:29

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-10 : 00:49:35

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-11 : 07:43:40

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources