Order by Issue - 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

General SQL Server Forums

New to SQL Server Programming

Order by Issue

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-14 : 15:58:31

Hi,

it's me again:)

Don't be afraid because my sql statement is long but easy:

select * from dbo.DAuditnew Where DataID in (select DataID from dbo.dtree A1 where A1.DataID in (select DataID from dbo.dtreeancestors where AncestorID = '434583') AND A1.Subtype = '144' ) AND AuditID = '10' AND ValueKey = 'LDD (controlled)[1].Confidentality[1]'

My problem is a logical thing. Everthing works fine and I get 10 rows as result. The DataID (Object ID) is only from two items...so 5 rows are item 1 with 5 different Modify dates...With Item two it's the same. What I want. is the last entry of each item.I tried it as follows:

select Max(Modifydate) from dbo.DAuditnew Where DataID in (select DataID from dbo.dtree A1 where A1.DataID in (select DataID from dbo.dtreeancestors where AncestorID = '434583') AND A1.Subtype = '144' ) AND AuditID = '10' AND ValueKey = 'LDD (controlled)[1].Confidentality[1]'

The result is only one row from one item. Better two say from the last...how can I get the last entry from each item (DataID)?

Kind regards,

Lara

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-07-14 : 17:31:30

Hi,

Have slightly rewritten your sql. Hope this works for you:


select e.*
  from (select a.DataID
              ,max(Modifydate) as Modifydate
          from dbo.DAuditnew as a
               inner join dbo.dtree as b
                       on b.DataID=a.DataId
                      and b.Subtype='144'
               inner join dbo.dtreeancestors as c
                       on c.DataID=b.DataId
                      and c.AncestorID='434583'
         where a.AuditID='10'
           and a.ValueKey='LDD (controlled)[1].Confidentality[1]'
         group by a.DataID
       ) as d
       inner join dbo.DAuditnew as e
               on e.DataID=d.DataID
              and e.Modifydate=d.Modifydate

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-14 : 19:31:06

HI,

thanks for your help.

it's working almaost correct.

As result I get 4 rows. It seems that the follwing part

a.ValueKey='LDD (controlled)[1].Confidentality[1]'

is ignored.

In Europe it's pretty late and i could'n find the errir..i will ry it late to find it

Kind reagrds

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-14 : 19:47:49

[code]

select e.*
from (select a.DataID
,max(Modifydate) as Modifydate
from dbo.DAuditnew as a
inner join dbo.dtree as b
on b.DataID=a.DataId
and b.Subtype='144'
inner join dbo.dtreeancestors as c
on c.DataID=b.DataId
and c.AncestorID='434583'
where a.AuditID='10'
and a.ValueKey='LDD (controlled)[1].Confidentality[1]'
group by a.DataID
) as d
inner join dbo.DAuditnew as e
on e.DataID=d.DataID
and e.Modifydate=d.Modifydate
Where e.AuditID='10'
and e.ValueKey='LDD (controlled)[1].Confidentality[1]'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-15 : 04:06:08

Hi,

Thanks..Yes ir was late....A new where clause was the key..thank you very much.

Kind regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-15 : 22:14:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-09 : 07:44:23

[code];WITH cteSource({column list here}, rn)
AS (
SELECT an.{column list here},
ROW_NUMBER() OVER (PARTITION BY an.DataID ORDER BY an.ModifyDate DESC) AS rn
FROM dbo.dAuditNew AS an
INNER JOIN dbo.dTree AS t ON t.DataID = an.DataID
AND t.SubType = '144'
INNER JOIN dbo.dTreeAncestors AS ta ON ta.DataID = t.DataID
AND ta.AncestorID = '434583'
WHERE an.AuditID = '10'
AND an.ValueKey = 'LDD (controlled)[1].Confidentality[1]'
)
SELECT {column list here}
FROM cteSource
WHERE rn = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources