move where statement 1

[Brianfree]

Hi, im struggling to move the where statement to the end of the query so I don't need to use it within each select? Is it possible as I cant figure it out?

SELECT X.[Item Code], X.[Highest Price], Y.[Last Price] FROM

(SELECT TOP 1 T0.[ItemCode] AS [Item Code], T1.[Price] AS [Highest Price] FROM OITM T0 INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[ItemCode] = '12345' ORDER BY T1.[Price] DESC) X

INNER JOIN

(SELECT TOP 1 T0.[ItemCode] AS [Item Code], T1.[Price] AS [Last Price] FROM OITM T0 INNER JOIN INV1 T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[ItemCode] = '12345' ORDER BY T1.[DocDate] DESC) Y

ON X.[Item Code] = Y.[Item Code]

Many thanks

Brian

 

[feherke]

Hi

Should be simple like this :

[b]SELECT[/b] X.[Item Code], X.[Highest Price], Y.[Last Price] [b]FROM[/b]

([b]SELECT[/b] [b]TOP[/b] 1 T0.[ItemCode] [b]AS[/b] [Item Code], T1.[Price] [b]AS[/b] [Highest Price] [b]FROM[/b] OITM T0 [b]INNER[/b] [b]JOIN[/b] INV1 T1 [b]ON[/b] T0.ItemCode = T1.ItemCode [b]ORDER[/b] [b]BY[/b] T1.[Price] [b]DESC[/b]) X

[b]INNER[/b] [b]JOIN[/b]

([b]SELECT[/b] [b]TOP[/b] 1 T0.[ItemCode] [b]AS[/b] [Item Code], T1.[Price] [b]AS[/b] [Last Price] [b]FROM[/b] OITM T0 [b]INNER[/b] [b]JOIN[/b] INV1 T1 [b]ON[/b] T0.ItemCode = T1.ItemCode [b]ORDER[/b] [b]BY[/b] T1.[DocDate] [b]DESC[/b]) Y

[b]ON[/b] X.[Item Code] = Y.[Item Code]

[b]WHERE[/b] X.[Item Code] = [green][i]'12345'[/i][/green]

Feherke.
feherke.ga

 

[imex]

Brian,

I believe that to get the desired result is better to keep the Where within each Select.

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[feherke]

Hi

You are right, imex. I missed the [tt]top 1[/tt]. Of course, those also need to be moved to the outer [tt]select[/tt]. Which means, neither the [tt]order by[/tt] clauses can remain inside.

[b]SELECT[/b] [b]TOP[/b] 1 X.[Item Code], X.[Highest Price], Y.[Last Price] [b]FROM[/b]

([b]SELECT[/b] T0.[ItemCode] [b]AS[/b] [Item Code], T1.[Price] [b]AS[/b] [Highest Price] [b]FROM[/b] OITM T0 [b]INNER[/b] [b]JOIN[/b] INV1 T1 [b]ON[/b] T0.ItemCode = T1.ItemCode) X

[b]INNER[/b] [b]JOIN[/b]

([b]SELECT[/b] T0.[ItemCode] [b]AS[/b] [Item Code], T1.[Price] [b]AS[/b] [Last Price] [b]FROM[/b] OITM T0 [b]INNER[/b] [b]JOIN[/b] INV1 T1 [b]ON[/b] T0.ItemCode = T1.ItemCode) Y

[b]ON[/b] X.[Item Code] = Y.[Item Code]

[b]WHERE[/b] X.[Item Code] = [green][i]'12345'[/i][/green]

[b]ORDER[/b] [b]BY[/b] X.[Highest Price] [b]DESC[/b]

By the way, personally neither I would rewrite the original query.

Feherke.
feherke.ga

 

[gmmastros]

I'm a little confused about this, which means that I may not understand it. If this is the case, please ignore this post.

I'm looking the the code and I think it can be a lot more efficient. I say this, knowing full well, that you may not notice a difference in performance because fast is fast.

The first derived table looks like this:

SELECT  TOP 1 
        T0.[ItemCode] AS [Item Code], 
        T1.[Price] AS [Highest Price] 
FROM    OITM T0 
        INNER JOIN INV1 T1 
          ON T0.ItemCode = T1.ItemCode 
WHERE   T0.[ItemCode] = '12345' 
ORDER BY T1.[Price] DESC

Now, here's where the confusion comes in.... This code is obviously using 2 tables (OITM and INV1). Both tables have a column named ItemCode. The OITM table (aliased T0) is used to return the item code, used in the join and used in the filter. Since this is an inner join, every row returned (ignoring the TOP 1 and where condition for a moment) will have the same value from either table. This means that you can return the T1.ItemCode instead of T0.ItemCode and get exactly the same results. Similarly, you can use T1.ItemCode in the where clause to get the same results. Making the replacements, the code would look like this:

SELECT  TOP 1 
        T1.[ItemCode] AS [Item Code], 
        T1.[Price] AS [Highest Price] 
FROM    OITM T0 
        INNER JOIN INV1 T1 
          ON T0.ItemCode = T1.ItemCode 
WHERE   T1.[ItemCode] = '12345' 
ORDER BY T1.[Price] DESC

Ok.... so you're thinking.... so what. Ordinarily I would agree with you. However, in this situation, T0 isn't used at all, so you can remove it from the query, like this:

SELECT  TOP 1 
        T1.[ItemCode] AS [Item Code], 
        T1.[Price] AS [Highest Price] 
FROM    INV1 T1 
WHERE   T1.[ItemCode] = '12345' 
ORDER BY T1.[Price] DESC

Similarly, the 2nd derived table can be replaced with this:

SELECT   TOP 1 
         T1.[ItemCode] AS [Item Code], 
         T1.[Price] AS [Last Price] 
FROM     INV1 T1 
WHERE    T1.[ItemCode] = '12345' 
ORDER BY T1.[DocDate] DESC

Taking another look... It's obvious that ItemCode is returned so that we can join the results. Furthermore, since both derived tables have a TOP 1 in them, the entire query can only ever return 1 row with item code, highest price, and last price. The first derived table returns the highest price and the second returns the last price. The key here is that we can treat these as scalar values if we wanted to, so the whole query would look like this.

SELECT '12345' As [Item Code], 
        (	
        SELECT TOP 1 
               T1.[Price] AS [Highest Price] 
        FROM   INV1 T1 
        WHERE  T1.[ItemCode] = '12345' 
        ORDER BY T1.[Price] DESC
        ) As [Highest Price],
        (
        SELECT  TOP 1 
                T1.[Price] AS [Last Price] 
        FROM    INV1 T1 
        WHERE   T1.[ItemCode] = '12345' 
        ORDER BY T1.[DocDate] DESC) As [Last Price]

Basically, the derived tables are replaced by subqueries in the select clause. This is really just a simpler (and therefore more efficient) method of combining the 3 scalar values to a single row with 3 columns.



-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

 

[Brianfree]

Hi thanks for your help - starting to make sence now! BTW, if I have multiple results returned for the highest price, how do I only return the last highest price?

Example Results

Price, Item, DocDate
34.00, 12345, 11/09/13
34.00, 12345, 18/09/13
34.00, 12345, 25/09/13
29.75, 12345, 29/08/13
29.75, 12345, 04/09/13

Many thanks

Brian

 

[gk53]

return the last highest price?"
if we have
29.75, 12345, 29/09/13
and
34.00, 12345, 25/09/13
which one it should be?

 

[Brianfree]

Hi, would need the highest price so would be 34.00, if there are multiple records with the same highest price then only the most recent highest price is to be displayed - so reducing multiple rows of data..

Sorry for being a pain, I could give you the entire query im working on but its a SAP B1 query using non standard sql..

Many thanks

Brian

 

[gk53]

something like that

select MAX(Price) as maxPrice, MIN(price) as minPrice, DDate as maxDate
from @T t
	join 
	(
	select MAX(DocDate) as ddate
		from @T
		where Item= '12345'
	) d
	on t.DocDate = d.ddate
where Item= '12345'
group by DDate

 

[drlex]

Hmmm - I don't think that last code is right; add in a record 29.75, 12345, 29/09/13 and that will show as the latest date for 34.00

Try this modification:

select MAX(DocDate) as LastDate, t.price as MaxPrice
from @T t
	join 
	(
	select MAX(Price) as price
		from @T
		where Item= '12345'
	) d
	on t.price = d.price
where Item= '12345'
group by t.price

soi là, soi carré

 

[gmmastros]

This is how I would do it.

set language british

Declare @Temp Table(Price Decimal(10,2), item int, DocDate DateTime)

Insert Into @Temp Values(34.00,	12345, '11/09/13')
Insert Into @Temp Values(34.00,	12345, '18/09/13')
Insert Into @Temp Values(34.00,	12345, '25/09/13')
Insert Into @Temp Values(29.75, 12345, '29/08/13')
Insert Into @Temp Values(29.75, 12345, '04/09/13')

; With Data As(
    Select Item, 
           Price, 
           DocDate, 
           Row_Number() Over (Partition By Item Order By Price DESC, DocDate DESC) As RowId
    From   @Temp
)
Select * 
From   Data 
Where  RowId = 1

Note that the Set Language part is only in there so the dates get entered properly. Also note that the first part of this query loads data in to a table variable solely for testing purposes. Once you understand how this code works, you would want to change it to use your actual data instead of the table variable.


-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

 

[gk53]

drlex it will show last date record added , min and max price, as I mention in my post I'm not sure if it is what Brian needs, but it make sense

 

[gk53]

gmmastros your solution will work for find max Price and date when it was offered, but it seems like he need max and last price...
so

select MAX(Price) as maxPrice, d.price as LastPrice, DDate as maxDate
from @T t
	join 
	(
	select MAX(DocDate) as ddate, price
		from @T
		where Item= '12345'
                group by price
	) d
	on t.DocDate = d.ddate
where Item= '12345'
group by DDate

produce what Brian asked

 

[gmmastros]

gk53,

I took today's comment as though it were a separate question. If I misunderstood, then I apologize. However... my query does return the results that Brian stated he wants.

-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

 

[Geates]

Sorry for highjacking but..

what are the square braces around column names for?

SELECT  TOP 1 
        T1.[ItemCode] AS [Item Code], 
        T1.[Price] AS [Highest Price] 
FROM    INV1 T1 
WHERE   T1.[ItemCode] = '12345' 
ORDER BY T1.[Price] DESC

and what is the @ sign for?

Declare @Temp Table(Price Decimal(10,2), item int, DocDate DateTime)

Insert Into @Temp Values(34.00,	12345, '11/09/13')
Insert Into @Temp Values(34.00,	12345, '18/09/13')
Insert Into @Temp Values(34.00,	12345, '25/09/13')
Insert Into @Temp Values(29.75, 12345, '29/08/13')
Insert Into @Temp Values(29.75, 12345, '04/09/13')

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

 

[gk53]

1. @ stands for variable. We do not have actual table in our system to model data, So I declare variable
Declare @Temp Table(Price Decimal(10,2), item int, DocDate DateTime)
and use that variable instead of original OITM table
2. What are the square braces around column names for?
Square braces is optional if your fields name is one word and not using reserved words, but in original post
T1.[Price] AS [Highest Price] for Highest Price it is required and just for consistancy added to all fields name

 

[gmmastros]

The square brackets allow you to use reserved words in your table and column names. It also allows you to use spaces in column names.

T1.[ItemCode] AS [Item Code]

The first set of square brackets are not necessary because ItemCode is not a reserved word. The second set of square brackets is necessary because the aliased column name has a space in it. It is best practice to not use reserved keywords for table and column names, but is sometimes unavoidable for legacy reasons.

Imagine you have a table named from with a column named select. Now imagine what a simple query would look like.

SELECT Select
FROM   From

As you can image, SQL Server would have major issues with this code. To correct way to query this horribly named table would be...

SELECT [Select]
FROM   [From]

The @ symbol is special to SQL Server. It basically declares a memory variable. If you wanted to use a scalar variable in your code, or a parameter to a stored procedure, you use the @ symbol. Ex:

Declare @MyVariable Int

In the code above, you are creating a variable named @MyVariable with a data type of integer.

In the context of your question... @Temp is referencing a table variable. Most of the time, people think of a variable as a single piece of data (like EyeColor = Blue or Brown). With SQL Server, you can create table variables that allow you to store multiple columns and multiple rows. Basically, this is creating a table in *memory. When the code is done executing, the table variable automatically disappears.

*memory: Note that table variables are not only stored in memory. They do take some space in TempDB.

I often use table variables to "dummy up" sample data. It allows me to test code before posting an answer here. I also use table variables in some of my production code. They come in handy to store intermediate results. In fact they are similar to temp tables (tables with a # sign in front of them).



-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

 

[Geates]

This makes complete sense. Thank you, both!

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm