T-SQL Group By problem in a Nested Select

[tonys123]

Hi

I am doing a query against a relatively large table. The basic query is as follows:

select aud.record_id, aud.auditdatetime, seqno,
substring(cast(aud.oldrecord as varchar(max)), (dbo.fnNthIndex(aud.oldrecord,'|',8) + 6), 4),
substring(cast(aud.newrecord as varchar(max)), (dbo.fnNthIndex(aud.newrecord,'|',8) + 6), 4)
from dbC_AUDITTRAIL_USERTABLES aud, dbo.PEOPLE p
where
p.PEOPLE_ID = aud.RECORD_ID
and aud.TABLENAME like 'TABLENAME'
and p.LASTNAME like 'Lastname'
order by aud.auditdatetime desc
​

This query returns 10 records from the entire table which is around 1 million records. The response is instant. But I only want those records where the 2 last columns are different (the 2 rows in the select that start with substring and are highlighted in red). When I add

AND substring(cast(aud.oldrecord as varchar(max)), (dbo.fnNthIndex(aud.oldrecord,'|',8) + 6), 4) <> substring(cast(aud.newrecord as varchar(max)), (dbo.fnNthIndex(aud.newrecord,'|',8) + 6), 4)
​

to my WHERE statement it disappears off in to space.

To get around this I tried to add in a nested select as follows:

select aud.record_id, aud.auditdatetime, seqno,
substring(cast(aud.oldrecord as varchar(max)), (dbo.fnNthIndex(aud.oldrecord,'|',8) + 6), 4),
substring(cast(aud.newrecord as varchar(max)), (dbo.fnNthIndex(aud.newrecord,'|',8) + 6), 4)
from dbC_AUDITTRAIL_USERTABLES aud, dbo.PEOPLE p ,
(select record_id,
substring(cast(oldrecord as varchar(max)), (dbo.fnNthIndex(oldrecord,'|',8) + 6), 4),
substring(cast(newrecord as varchar(max)), (dbo.fnNthIndex(newrecord,'|',8) + 6), 4)
from dbC_AUDITTRAIL_USERTABLES group by RECORD_ID ) maxres
where
p.PEOPLE_ID = aud.RECORD_ID
and aud.RECORD_ID = maxres.record_id
and aud.TABLENAME like 'TABLENAME'
and p.LASTNAME like 'Lastname'
order by aud.auditdatetime desc
​

This gives me the following errors:

Msg 8120, Level 16, State 1, Line 6
Column 'dbC_AUDITTRAIL_USERTABLES.OLDRECORD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'dbC_AUDITTRAIL_USERTABLES.OLDRECORD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8155, Level 16, State 2, Line 9
No column name was specified for column 2 of 'maxres'.
Msg 8155, Level 16, State 2, Line 9
No column name was specified for column 3 of 'maxres'.
​

I never got as far as putting in the line to compare the 2 columns. I know the problem is to do with grouping in the nested select but no matter what I do I can't get it to work - can anyone help please?

Thanks

Anthony

 

[gmmastros]

The easiest method would be....

; With Data As
(
  select  aud.record_id, 
          aud.auditdatetime, 
          seqno, 
          substring(cast(aud.oldrecord as varchar(max)), (dbo.fnNthIndex(aud.oldrecord,'|',8) + 6), 4) As OldData, 
          substring(cast(aud.newrecord as varchar(max)), (dbo.fnNthIndex(aud.newrecord,'|',8) + 6), 4) As NewData
  from    dbo.OC_AUDITTRAIL_USERTABLES aud, 
          dbo.PEOPLE p 
  where	  p.PEOPLE_ID = aud.RECORD_ID
          and aud.TABLENAME like 'TABLENAME' 
          and p.LASTNAME like 'Lastname'
)
Select	*
From	Data
Where	OldData <> NewData
order by aud.auditdatetime desc

This query should take the same amount of time as the original query +/- a couple dozen nanoseconds.

If this works, and you would like me to explain, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonys123]

Hi George

Thanks - that did work but the problem is that it is too slow. It takes 8 minutes to return the results on just 1 'lastname' - I have 850 lastnames so as you can see the complete query will take an impractical amount of time.

Any ideas on how to make this fast?

Cheers

Anthony

 

[fredericofonseca]

try

if object_id('tempdb..#temp') is not null
   drop table #temp;

select  aud.record_id
     ,  aud.auditdatetime
     ,  seqno
     ,  substring(cast(aud.oldrecord as varchar(max)), (dbo.fnNthIndex(aud.oldrecord,'|',8) + 6), 4) As OldData
     ,  substring(cast(aud.newrecord as varchar(max)), (dbo.fnNthIndex(aud.newrecord,'|',8) + 6), 4) As NewData
into #temp
from dbo.OC_AUDITTRAIL_USERTABLES aud
inner join dbo.PEOPLE p 
  on  p.PEOPLE_ID = aud.RECORD_ID
  and aud.TABLENAME like 'TABLENAME' 
  and p.LASTNAME like 'Lastname'
;

select	*
from #temp aud
where aud.OldData <> aud.NewData
order by aud.auditdatetime desc 
;

if object_id('tempdb..#temp') is not null
   drop table #temp;

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[tonys123]

Awesome Frederico - that took only 1 second to complete.

Thank you!

Regards

Anthony