Pull data without changing the sequence of elements in the lookup table

Question

I want to see the values ​​listed in the temp table:

So let's say:

Create Table #mylist ( eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS, refdate datetime ) Insert Into #mylist (eserial, refdate) Values ('A', '2015-09-15') Insert Into #mylist (eserial, refdate) Values ('B', '2015-09-14') Insert Into #mylist (eserial, refdate) Values ('C', '2015-09-13') Insert Into #mylist (eserial, refdate) Values ('D', '2015-09-12') 

I need the result to be Top 1 less than the key date.
And should be returned in the same sequence as in the temporary table.

What I tried:

 Select lst.eserial, lst.refdate, app.CREATEDDATETIME, From #mylist lst Outer Apply (Select Top 1 rec.CREATEDDATETIME, rec.ESERIAL, rec.ITEMID From TableSource rec Where lst.eserial=rec.ESERIAL And rec.CREATEDDATETIME<lst.refdate Order By rec.CREATEDDATETIME Desc ) As app 

It works, but it is slow. Also, if the number of rows increases, it does not save the eserial sequence. I need a query to keep the order that I put in the temporary table.

Again my expected result is simple:

Where eserial is the same sequence as the temporary table, and CREATEDDATETIME is the maximum date less than the CREATEDDATETIME date. More like conditional Vlookup if you know Excel.

Answer 1

It’s not clear what you mean by

maintain a sequence of elements in a temporary table

but if you want to get the result ordered by eserial , you need to add ORDER BY eserial to your query. Without ORDER BY resulting rows can be returned in any order. This applies to any method you choose.

So, taking your last request as a basis, it will look like this:

 Select lst.eserial ,lst.refdate ,app.CREATEDDATETIME From #mylist lst Outer Apply ( Select Top 1 rec.CREATEDDATETIME From TableSource rec Where lst.eserial=rec.ESERIAL And rec.CREATEDDATETIME<lst.refdate Order By rec.CREATEDDATETIME Desc ) As app ORDER BY lst.eserial; 

To make it work quickly and efficiently, add the TableSource index to (ESERIAL, CREATEDDATETIME) . The order of the columns in the index is important.

It is also important to know if there are any other columns that you use in the OUTER APPLY query and how you use them. You mentioned the AREAID column in the first version in the question, but not in the last version. If you have more columns, then clearly show how you are going to use them, because the correct index depends on it. An index on (ESERIAL, CREATEDDATETIME) enough for the query I wrote above, but if you have more columns, you may need a different index.

It will also help the optimizer if you defined your temporary table using PRIMARY KEY :

 Create Table #mylist ( eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS PRIMARY KEY, refdate datetime ) 

The primary key will create a unique clustered index.

Another important point. What is the type and sorting of the eserial and CREATEDDATETIME in the main TableSource ? Verify that the types and collation of the columns in the temp table matches the main table, TableSource . If the type is different ( varchar vs. nvarchar or datetime vs. date ) or the sorting is different from the index, it cannot be used => it will be slow.

Edit

You use the phrase “the same sequence as the temporary table” several times in the question, but it’s not entirely clear what you mean by that. Your sample data does not help to eliminate ambiguity. The eserial column eserial also adds to the confusion. I see two possible values:

• Return rows from temp table sorted by values ​​in the eserial column.
• Returns the rows from the temp table in the same order in which they were inserted.

My initial answer implies (1): it returns the rows from the temp table, sorted by the values ​​in the eserial column.

If you want to keep the order of the rows when they were inserted into the table, you need to explicitly remember this order. The easiest way is to add the IDENTITY column to the temp table and then arrange that column. Like this:

 Create Table #mylist ( ID int IDENTITY PRIMARY KEY, eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS, refdate datetime ) 

And in the last query use ORDER BY lst.ID

Answer 2

It is easy to use personality. A query without Order does not guarantee order on the SQL server.

 Create Table #mylist ( seqId int identity(1,1), eserial nvarchar(35) Collate SQL_Latin1_General_CP850_CI_AS, refdate datetime ) 

Use the table freely and put Order By seqId at the end of your query

Edit

Use MAX() instead of TOP 1 with order if you don't have a cluster index on ESERIAL , CREATEDDATETIME on TableSource

fooobar.com/questions/215611 / ...

 Select lst.eserial, lst.refdate, app.CREATEDDATETIME, From #mylist lst Outer Apply ( Select MAX(rec.CREATEDDATETIME), rec.ESERIAL, rec.ITEMID From TableSource rec Where lst.eserial = rec.ESERIAL And rec.CREATEDDATETIME < lst.refdate GROUP BY rec.ESERIAL, rec.ITEMID ) As app ORDER BY lst.seqId 

Answer 3

Perhaps the performance issue is related to indexing. Try adding the indexes below by removing UNIQUE if the keys are not unique.

 CREATE UNIQUE NONCLUSTERED INDEX idx ON #mylist (eserial, refdate); CREATE UNIQUE NONCLUSTERED INDEX idx ON TableSource (eserial, CREATEDDATETIME);