Union and Sql Statement

[rustyd]

I have a customer lookup screen. We set it up to select max 500 records from anywhere in the table. If the user includes some text for searching though, we select the 250 before and the 250 after the search phrase. Here is an example:

 

SELECT TOP 250 Item,Description FROM Inventory WHERE Item <= 'FR' 
UNION 
SELECT TOP 250 Item,Description FROM Inventory WHERE Item >= 'FR' ORDER BY Item

 

If for instance the records before the first instance of FR would be:

 

Rec 400: Example98

Rec 401: Example99

Rec 402: Free1

Rec 403: Free2

 

But the result I got was not what I was looking for. I do not receive the 4 records in my results. I get records 1-250 and records 402 to 651.

 

What I want is records 151-651. Any ideas?

 

My thoughts are a union isn't going to work for what i need, I may need to make 2 separate calls.

[pelikan]

think about the first WHERE clause - top 250 < x

starts with first record so you get 1 .. 250. This suggests you

should use an ORDER BY in the first select to reverse the sort order.

[Joe Mamma]

and you will have to use a derived table statement for the first select:

 

[b]select * [/b]
[b]from ([/b]
SELECT TOP 250 Item, Description 
FROM Inventory 
WHERE Item <= 'FR' 
order by item desc[b]) tempinventory
[/b] UNION 
SELECT TOP 250 Item, Description 
FROM Inventory WHERE Item >= 'FR' ORDER BY Item

 

else the union will fail. . .

 

good call pel!

[rustyd]

Thanks. That did the trick.

[rustyd]

Not so fast!! When I add a where clause to get 250 records before and 250 records after a particular vendor, I say

 

SELECT * FROM (SELECT TOP 250 Item,Description,Vendor,Category,Style,Picturefilename FROM Inventory WHERE Vendor  <= 'XYZ' ORDER BY Vendor Desc, Sku DESC) TempInv1
UNION 
SELECT * FROM (SELECT TOP 250 Item,Description,Vendor,Category,Style,Picturefilename FROM Inventory WHERE Vendor  >= 'XYZ' ORDER BY Vendor, Sku) TempInv2 ORDER BY Vendor, Sku

 

I have 414 items with a vendor of XYZ. That is how many records the query returns. If I change the top 250 to top 90, I get 180 records of items with the vendor XYZ. The problem is they are the first 90 and the last 90.

 

The way it looks now, I'm going to have to use 2 separate recordsets and the first one will only have a less than in the where clause.

 

What do you guys think?

[Joe Mamma]

all is not lost. . . this calls for table hints!!!

 

run this in query analyzer. . .

-- dummy table for example:
create table foobar(foo int, bar datetime);
go 

--typical ascending index. . . 
create index foo_asc on foobar(foo asc)
go 

--we need a descending index for our union query hint. . . 
create index foo_desc on foobar(foo desc)
go


--lets populate the table. . . 
declare @n int
set @n = 1
while @n <= 100 
begin
insert into foobar values(floor(rand() * 100), GetDate())
select @n = @n+1 
end 
go

[b][font=Arial Black]--use table hints to specify the order we select from. . .[/font] [/b]

select top 10 * from foobar [b][font=Arial Black]with ( index(foo_desc))[/font][/b] where foo <= 50
union 
select top 10 * from foobar [b][font=Arial Black]with ( index(foo_asc))[/font][/b] where foo >= 50

[Joe Mamma]

a more relevant example. . . (table simplified for data population by defining all fields as ints. . .

 

again run this in query analyzer. . .

-- dummy table for example:
create table Inventory(Item int identity not null primary key, sku int ,Vendor int);  
go 

--typical ascending index. . . 
create index inv_asc on Inventory(vendor asc, sku asc );
go 

--we need a descending index for our union query hint. . . 
create index inv_desc on Inventory(vendor desc, sku desc );
go


--lets populate the table. . . 
declare @n int
set @n = 1
while @n <= 1000 
begin
insert into Inventory(sku, vendor) values(floor(rand() * 1000), floor(rand() * 1000))
select @n = @n+1  
end 

go

[b][font=Arial Black]--use table hints to specify the order we select from. . .[/font] [/b]
select * from
(
	SELECT TOP 90 Item,Vendor, sku 
		FROM Inventory 
			[font=Arial Black]with(index(inv_desc)) [/font]
		WHERE Vendor  <= 540 
UNION 
	SELECT TOP 90 Item,Vendor,sku 
		FROM Inventory 
			[font=Arial Black]with(index(inv_asc))[/font] 
		WHERE Vendor  >= 540
) tempinv 
order by vendor

[bri189a]

I have no familiarity with table hints, and what I don't quite get is...you're saying that specifyng an index like that pulls it in sorted already, okay, understand that - what kind of performance implications does this have when you dynamically add it and have it in a stored procedure, or is it better to have before hand if you know you're going to do it a lot. Do you have to 'drop' the index afterwards - if you do such a thing? What are some other caveats?

 

Is this a pretty standard ANSI SQL thing, or an SQL server thing only?

 

Good thing to know about, thanks, and thanks again for any further info you have.

[Joe Mamma]

well, the code posted was not for a stored procedure. . . the DDL (everything up to the bold comments) was to get the data ready for the union query. . .

 

Dynamically adding the indexes could dog the approach depending on size of the table.

 

In the case of the original question, I would permanently set the index up as part of the database.

 

I don't know if table hints are part of the ANSI standard, though I do know that oracle has them and I would bet that DB2 universal, and sybase do as well. . . but the syntax would probably vary (i know it does for oracle)

 

At any rate. . . if you need to sort a table desc, unless you have a descending index, a complete table scan is done (very slow.) and the fact that the same fields are being selected ascending and descending, table hints are almost imperative as you need to force the use of particular indexes to get the desired result.

[bri189a]

Cool. Thanks.