Top N sub query workaround - Does it exist?

Question

Top N sub query workaround - Does it exist?

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 1

GilaMonster (1/16/2009)
Jeff Moden (1/16/2009)
GilaMonster (1/16/2009)
If the comparison with the outer query is an equality, the subquery won't be processed row by row.
I pretty sure it will with an ORDER BY...
Order by where? In the subquery? In the outer query?
I'll test, I have code set up. I'm just not sure what to add where.

Look in the original post on this thread for that answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Ah, that one.

SELECT productId, productnumber,

(SELECT top 1 LineTotal FROM Sales.SalesOrderDetail sd WHERE sd.productID = p.productid order by sd.ModifiedDate)

FROM Production.Product p

WHERE ProductNumber like 'bk%'

The read of sales order detail is only done once, but there is an index spool (eager spool) that's running once for each row of the outer query. So it's still not quite running the entire subquery once per row (if it was, there should be 97 scans of that table), but close.

Ok, so inequalities or Top 1 .. order by subqueries

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 3

Hi everyone, thanks again for all your help.

I tried Ramesh's suggestion first, but to my surprise, it was only slightly quicker.

Was then going to try Chris Morris's suggestion but I'm not sure if using max(userID) will really give me the top 1 userID. If my userID's are intials won't it give me RGC before ABC, when if ABC is the top userID I want ABC not RGC? Sorry I know that's kinda hard to follow..

I'm looking at Gails suggestion, but to be honest I don't think I have the skill to implement that correctly (that's embarrassing)

So I'll try Chris's now..and report back

Thanks again

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Oops...try MIN as well!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

krypto69 (1/16/2009)
I'm looking at Gails suggestion, but to be honest I don't think I have the skill to implement that correctly (that's embarrassing)

What suggestion? The query bits I posted were on correlated subqueries in general, not specific solutions to your post. Chris had a perfectly good solution

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 6

krypto69 (1/16/2009)
Hi everyone, thanks again for all your help.
I tried Ramesh's suggestion first, but to my surprise, it was only slightly quicker.
Was then going to try Chris Morris's suggestion but I'm not sure if using max(userID) will really give me the top 1 userID. If my userID's are intials won't it give me RGC before ABC, when if ABC is the top userID I want ABC not RGC? Sorry I know that's kinda hard to follow..
I'm looking at Gails suggestion, but to be honest I don't think I have the skill to implement that correctly (that's embarrassing)
So I'll try Chris's now..and report back
Thanks again

Would you post the CREATE TABLE statement for the #Events table and include, say, 10 rows of data in the form of Insert statements? See the link in my signature for a pretty easy way to do that. I also need to know a bit more about the data so I can try to duplicate your environment with some little bit of precision... How many rows are in the #Events table and what is the average number of rows returned for this part of the query...

SELECT userID FROM JBMTest e2

WHERE e2.coCRN = e.coCRN AND e2.deptCRN = e.deptCRN AND e2.county = e.county AND e2.orderNo = e.orderNo

The reason why I need that information is because your original update is actually updating a column being returned by the correlated subquery in you code and that sometimes drives SQL Server nuts. I'm convinced there's a high speed method (and, [font="Arial Black"]I believe that Gail's fine code IS that solution[/font]) to pull this off, but I don't want to offer an untested nor uncompared solution.

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krypto69 SSChampion Points: 13604 More actions · Answer 7

Thanks everyone...VERY much appreciated.

I ended up doing this...and it seemed to work well.

What do you all think?

UPDATE e

SET e.UserID = q.UserID

FROM #Events AS e

INNER JOIN (

SELECT d.coCRN,

d.deptCRN,

d.county,

d.orderNo,

d.userID

FROM (

SELECT coCRN,

deptCRN,

county,

orderNo,

userID,

ROW_NUMBER() OVER (PARTITION BY coCRN, deptCRN, county, orderNo ORDER BY logID) AS recID

FROM #Events

) AS d

WHERE d.recID = 1

) AS q ON q.coCRN = e.coCRN

AND q.deptCRN = e.deptCRN

AND q.county = e.county

AND q.orderNo = e.orderNo

krypto69 SSChampion Points: 13604 More actions · Answer 8

The method I posted above worked...but gives me fewer records...so I'm stuck..

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 9

krypto69 (1/17/2009)
The method I posted above worked...but gives me fewer records...so I'm stuck..

Heh... we are too... you've not posted the CREATE TABLE statements I asked for and you've not posted any readily consumable test data according to the link in my signature below like I asked before.

Good luck.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krypto69 SSChampion Points: 13604 More actions · Answer 10

Sorry for the delay Jeff,

I followed the directions in your signature. Let me know if this is incorrect.

CREATE TABLE [dbo].[EventItems_rob](

[LogId] [int] NOT NULL,

[Company] [varchar](50) NOT NULL,

[CompanyId] [int] NOT NULL,

[Department] [varchar](50) NOT NULL,

[DepartmentId] [int] NOT NULL,

[CompanyCRN] [varchar](50) NOT NULL,

[DepartmentCRN] [varchar](50) NOT NULL,

[TitleUnit] [varchar](50) NOT NULL,

[TitleUnitId] [int] NOT NULL,

[State] [varchar](50) NOT NULL,

[County] [varchar](100) NOT NULL,

[OrderNo] [varchar](128) NOT NULL,

[OrderId] [int] NOT NULL,

[UserCompany] [varchar](50) NOT NULL,

[UserCompanyId] [int] NOT NULL,

[UserName] [varchar](50) NOT NULL,

[UserId] [int] NOT NULL,

[Source] [varchar](50) NULL,

[Event] [varchar](256) NULL,

[EventTime] [datetime] NOT NULL,

[EventValue] [varchar](150) NULL,

[PI2Status] [varchar](256) NULL,

[PI2LineCount] [varchar](256) NULL,

[PI2RequestTrigger] [varchar](256) NULL,

[PI2PrimaryRequestValue] [varchar](256) NULL,

[Comment] [varchar](4096) NULL

) ON [PRIMARY]

Please refer to post below for sample data

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Almost. What do you want the results to look like?

p.s. Can you put that lot into a code block so it doesn't cause scrolling?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 12

It would be great if the results could match the output of the orignal statement, as this is part of a larger proc that I would prefer not to change.

As for the code block. I'm a little confused..I looked at the example again and I thought I matched it, but I'm guessing you want 'GO's in between the selects?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

krypto69 (1/18/2009)
As for the code block. I'm a little confused..I looked at the example again and I thought I matched it, but I'm guessing you want 'GO's in between the selects?

No. I'm asking you to wrap it in [ code ] tags (remove spaces) as that should prevent the long rows from messing up the forum page's formatting. See ti IF shortcuts on the left had side of the reply screen.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

krypto69 SSChampion Points: 13604 More actions · Answer 14

Like this Gail?

INSERT INTO Eventitems_rob (LogId,Company,CompanyId,Department,DepartmentId,CompanyCRN,DepartmentCRN,TitleUnit,TitleUnitId,State,County,OrderNo,OrderId,UserCompany,UserCompanyId,UserName,UserId,Source,Event,EventTime,EventValue,PI2Status,PI2LineCount,PI2RequestTrigger,PI2PrimaryRequestValue,Comment)

SELECT '418504535','Chicago Title','134','ServiceLink PA','845','00021','00090','01','910','CA','Madera','1734995','33806768','ServiceLink','190','Christina Young','1538','TitlePoint','Image-Recorded','Sep 29 2008 12:53PM','MA:2008 00020606','','','','',''

SELECT '358982648','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553378','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:01AM','MA:2

SELECT '358982677','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553378','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:01AM','MA:2

SELECT '358981973','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553297','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 12:57AM','MA:2

SELECT '358982004','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553297','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 12:58AM','MA:2

SELECT '360906498','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020607','',

SELECT '359837936','PlantMaintenance','268','ManDATA','300','PLADM','00002','ManDATA','338','CA','Madera','[NONE]','29610978','PlantMaintenance','268','Mylene Mabalot','2907','TitlePoint','Image-Recorded','Jul 1 2008 5:37AM','MA:2008 00020608','','','','

SELECT '359837937','PlantMaintenance','268','ManDATA','300','PLADM','00002','ManDATA','338','CA','Madera','[NONE]','29610978','PlantMaintenance','268','Mylene Mabalot','2907','TitlePoint','Image-Recorded','Jul 1 2008 5:37AM','MA:2008 00020608','','','','

SELECT '359837616','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29610949','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jul 1 2008 5:32AM','MA:2

SELECT '359837618','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29610949','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jul 1 2008 5:32AM','MA:2

SELECT '377315426','Fidelity National Title','111','Fresno','789','00036','00036','01','795','CA','Madera','[NONE]','31023465','Fidelity National Title','111','Madrigal, Erica','6473','iView','Image-Recorded','Jul 28 2008 4:41PM','MA:2008 00020608','','',

SELECT '358982974','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553419','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:03AM','MA:2

SELECT '358982966','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553419','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:03AM','MA:2

SELECT '358983284','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553450','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:05AM','MA:2

SELECT '358983320','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553450','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:05AM','MA:2

SELECT '360906535','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020608','',

SELECT '358991385','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554412','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:48AM','MA:2

SELECT '358991391','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554412','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:48AM','MA:2

SELECT '358984854','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553529','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:13AM','MA:2

SELECT '358983991','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29553529','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:09AM','MA:2

SELECT '360906566','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020609','',

SELECT '358994270','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554725','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:04AM','MA:2

SELECT '358994274','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554725','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:04AM','MA:2

SELECT '377314804','Fidelity National Title','111','Fresno','789','00036','00036','01','795','CA','Madera','[NONE]','31023465','Fidelity National Title','111','Madrigal, Erica','6473','iView','Image-Recorded','Jul 28 2008 4:41PM','MA:2008 00020609','','',

SELECT '360906582','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020610','',

SELECT '369797534','PlantMaintenance','268','Chatsworth Locating','271','PLADM','00000','Chatsworth Locating','308','CA','Madera','[NONE]','30335099','PlantMaintenance','268','Frank Ortega','11871','TitlePoint','Image-Recorded','Jul 16 2008 4:43PM','MA:20

SELECT '369797597','PlantMaintenance','268','Chatsworth Locating','271','PLADM','00000','Chatsworth Locating','308','CA','Madera','[NONE]','30335099','PlantMaintenance','268','Frank Ortega','11871','TitlePoint','Image-Recorded','Jul 16 2008 4:43PM','MA:20

SELECT '360906599','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020611','',

SELECT '434439125','Lender Processing Services Default Title','1798','Irvine','1799','00061','00005','KH','2265','CA','Madera','80039553','35180524','Lender Processing Services Default Title','1798','Kimberly Forsythe','18773','iView','Image-Recorded','Oct

SELECT '409976056','Chicago Title','134','Irvine - Market Center','304','00021','00006','31','1419','CA','Madera','880537103','33376724','Title Production Services','170','FITTS006','3467','iView','Image-Recorded','Sep 16 2008 12:37PM','MA:2008 00020612','

SELECT '360906601','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020612','',

SELECT '360906621','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020613','',

SELECT '360906646','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020614','',

SELECT '360906659','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020615','',

SELECT '360906668','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020616','',

SELECT '360906675','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020617','',

SELECT '360906688','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020618','',

SELECT '360906718','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020619','',

SELECT '360906724','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020620','',

SELECT '360906737','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020621','',

SELECT '360906757','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020622','',

SELECT '360906765','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020623','',

SELECT '360906773','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020624','',

SELECT '358989040','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554040','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:36AM','MA:2

SELECT '358989041','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554040','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:36AM','MA:2

SELECT '359808093','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29608469','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jul 1 2008 2:37AM','MA:2

SELECT '359808106','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29608469','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jul 1 2008 2:37AM','MA:2

SELECT '358995252','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554808','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:09AM','MA:2

SELECT '358995259','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554808','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:09AM','MA:2

SELECT '360906788','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020625','',

SELECT '358987956','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554003','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:30AM','MA:2

SELECT '358987958','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29554003','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 1:30AM','MA:2

SELECT '381166734','Fidelity National Title','111','NDS','112','00036','00006','K','930','CA','Madera','75857705','31293356','Fidelity National Title','111','Kimberly Forsythe','12686','iView','Image-Recorded','Aug 1 2008 11:56PM','MA:2008 00020626','',''

SELECT '360906808','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020626','',

SELECT '383070654','Fidelity National Title','111','NDS','112','00036','00006','K','930','CA','Madera','75857705','31293356','Fidelity National Title','111','Twailla Malio','17805','iView','Image-Recorded','Aug 5 2008 10:15PM','MA:2008 00020626','','','',

SELECT '381166735','Fidelity National Title','111','NDS','112','00036','00006','K','930','CA','Madera','75857705','31293356','Fidelity National Title','111','Kimberly Forsythe','12686','iView','Image-Recorded','Aug 1 2008 11:56PM','MA:2008 00020627','',''

SELECT '360906817','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020627','',

SELECT '383070657','Fidelity National Title','111','NDS','112','00036','00006','K','930','CA','Madera','75857705','31293356','Fidelity National Title','111','Twailla Malio','17805','iView','Image-Recorded','Aug 5 2008 10:15PM','MA:2008 00020627','','','',

SELECT '360906828','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020628','',

SELECT '359001226','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555523','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:37AM','MA:2

SELECT '359001233','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555523','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:37AM','MA:2

SELECT '360906832','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020629','',

SELECT '360906836','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020630','',

SELECT '359002093','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555623','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:41AM','MA:2

SELECT '359002101','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555623','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:41AM','MA:2

SELECT '360906858','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020631','',

SELECT '359002482','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555670','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:42AM','MA:2

SELECT '359002484','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555670','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:42AM','MA:2

SELECT '360906894','PlantMaintenance','268','Fresno Locating','301','PLADM','00036','Fresno Locating','339','CA','Madera','[NONE]','29680450','PlantMaintenance','268','Lois Bowen','2650','iView','Image-Recorded','Jul 2 2008 1:41PM','MA:2008 00020632','',

SELECT '359002937','PlantMaintenance','268','Arizona Locating','1132','PLADM','00009','Arizona Locating','1224','CA','Madera','[NONE]','29555724','PlantMaintenance','268','PM2 Janice Marquez','9837','TitlePoint','Image-Recorded','Jun 30 2008 2:45AM','MA:2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

krypto69 (1/19/2009)
Like this Gail?

Exactly.

Would you mind editing your earlier post to do the same? It's nothing to do with the data or values, it's just to prevent the large code lines from messing up the forum's formatting and remove the need to scroll way to the right to find the reply button.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass