Only require one row based on MAX 1

[Cpreston]

Hi

I have a query that I am trying to get the information from product analysis table based on Max date.
I have 2 tables, Stocktranascations and Product.
I run the query below it brings back one row result giving max date, Product Code and Description. That is good.

SELECT DISTINCT MAX(dbo.StockTransaction.StockTransactionDate) AS [Max Date], dbo.Product.ProductCode, dbo.Product.Description
FROM         dbo.StockTransaction FULL OUTER JOIN
                      dbo.Product ON dbo.StockTransaction.ProductID = dbo.Product.ProductID
WHERE     (dbo.StockTransaction.StockTransactionType = 32) AND (dbo.Product.ProductID = 32012)
GROUP BY dbo.Product.ProductCode, dbo.Product.Description

However I need to show the StockActual field for that transaction which I held in stocktrasaction table, but when I add the file into the query it gives me 4 result rows and not just the one for the MAX Date and not just the one I expected.
I have tried different joins bt keep getting 4 rows instead of just the one with the MAX date. Could someone plese advise what I am doing wrong please and how to get the expected result, Thanks

SELECT DISTINCT MAX(dbo.StockTransaction.StockTransactionDate) AS [Max Date], dbo.Product.ProductCode, dbo.Product.Description, dbo.StockTransaction.StockActual
FROM         dbo.StockTransaction FULL OUTER JOIN
                      dbo.Product ON dbo.StockTransaction.ProductID = dbo.Product.ProductID
WHERE     (dbo.StockTransaction.StockTransactionType = 32) AND (dbo.Product.ProductID = 32012)
GROUP BY dbo.Product.ProductCode, dbo.Product.Description, dbo.StockTransaction.StockActual

 

[Andrzejek]

I would guess you have 4 different values in the last column: StockActual

Could you show the outcome of your last query?
And indicate which 1 row of data you want.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi

Yes I have attached an image, what I expect is just one row with the Maxdate 2016-01-25 with 336 in stockactual Thanks

 

[Andrzejek]

So you've got what you have asked for (which some times is not what you want )
You have 4 records, one for each StockActual

Before you had just one record with:
[tt]2016-01-25 13:31:00[/tt] as MaxDate, but since you added StockActual, now you have Max Date for every StockActual

Try:
[tt]
WHERE (dbo.StockTransaction.StockTransactionType = 32)
AND (dbo.Product.ProductID = 32012)[BLUE]
AND (dbo.StockTransaction.StockActual = '624.0000')[/BLUE]
[/tt]
to get the Max Date for that particular StockActual

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi
And in this case it is not what I want

What I want is only the row
2016-01-25 13:31:00 as MaxDate and the Stockactual which is 336. (so everything in row 2 only)

I do not want to see the other rows at all. By putting Max I assumed it would only give the row with the lastest date for a transaction.
when i take out the specific code I would only want to see the figures for the last transctions based on the last date a transaction happened of the type 32.

Thats where I want to get to.

Thanks

 

[gmmastros]

try this:

; With Data As
(
  Select  dbo.StockTransaction.StockTransactionDate, 
          dbo.Product.ProductCode, 
          dbo.Product.Description, 
          dbo.StockTransaction.StockActual,
          Row_Number() Over (Partition By dbo.Product.ProductCode Order By dbo.StockTransaction.StockTransactionDate) As RowId
  FROM    dbo.StockTransaction 
          FULL OUTER JOIN dbo.Product 
            ON dbo.StockTransaction.ProductID = dbo.Product.ProductID
  WHERE   (dbo.StockTransaction.StockTransactionType = 32) AND (dbo.Product.ProductID = 32012)
)
Select dbo.StockTransaction.StockTransactionDate, 
       dbo.Product.ProductCode, 
       dbo.Product.Description, 
       dbo.StockTransaction.StockActual
From   Data
Where  RowId = 1

If this works, and you want me to explain it, just 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

 

[Cpreston]

Hi

Thanks I will try the code tomorrow when i ahve access to the SQL database.
Would thios code also work if I took out the AND (dbo.Product.ProductID = 32012)

So it would list all the products but with their latest date transaction date. i am only working on one poductid currently just to get it working.
I will try tomorrow anyway

Many thanks for the great replys

 

[gmmastros]

Yes. It would still work. You will, of course, get multiple rows but there will only be 1 row per product code.

-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

 

[Cpreston]

Hi gmmastros

When I run the code I am getting the error below

Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "dbo.StockTransaction.StockTransactionDate" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "dbo.Product.ProductCode" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "dbo.Product.Description" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "dbo.StockTransaction.StockActual" could not be bound.


I will try and see if I can sort it during the day, but if you see the obvious please let me know

Many Thanks

 

[Olaf Doschke]

The table names are as given by you, you most probably have not selected the database of which the tables are members before test excuting the query in SSMS.

Bye, Olaf.

 

[Cpreston]

Hi

The table names are fine and I have the correct database selected to run the query against, doubled checked them all but stillg etting the problem

 

[Cpreston]

Hi

Double checked everything and the tablenames are ok and also using the query against the correct database but still getting

Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "dbo.StockTransaction.StockTransactionDate" could not be bound.
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "dbo.Product.ProductCode" could not be bound.
Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "dbo.Product.Description" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "dbo.StockTransaction.StockActual" could not be bound.

Thanks

 

[gmmastros]

My mistake, sorry. When selecting from a CTE, you don't prefix with schema or table name. Like this.

; With Data As
(
  Select  dbo.StockTransaction.StockTransactionDate, 
          dbo.Product.ProductCode, 
          dbo.Product.Description, 
          dbo.StockTransaction.StockActual,
          Row_Number() Over (Partition By dbo.Product.ProductCode Order By dbo.StockTransaction.StockTransactionDate) As RowId
  FROM    dbo.StockTransaction 
          FULL OUTER JOIN dbo.Product 
            ON dbo.StockTransaction.ProductID = dbo.Product.ProductID
  WHERE   (dbo.StockTransaction.StockTransactionType = 32) AND (dbo.Product.ProductID = 32012)
)
Select StockTransactionDate, 
       ProductCode, 
       Description, 
       StockActual
From   Data
Where  RowId = 1

-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

 

[Olaf Doschke]

OK, then sorry for bothering.

 

[Cpreston]

Hi

Great it runs now but unfortunately is not brining in the right result. It appears to be bringing in Row 3 where I am expecting Row 2 as 2016-01-25 was the last transaction date.
It is bringing in Row 3, any ideas please, thanks.

 

[gmmastros]

It's really hard sometimes to write code when you cannot test it.

try ordering StockTransactionDate DESC. Like this:

; With Data As
(
  Select  dbo.StockTransaction.StockTransactionDate, 
          dbo.Product.ProductCode, 
          dbo.Product.Description, 
          dbo.StockTransaction.StockActual,
          Row_Number() Over (
                     Partition By dbo.Product.ProductCode 
                     Order By dbo.StockTransaction.StockTransactionDate [!]DESC[/!]
                     ) As RowId
  FROM    dbo.StockTransaction 
          FULL OUTER JOIN dbo.Product 
            ON dbo.StockTransaction.ProductID = dbo.Product.ProductID
  WHERE   (dbo.StockTransaction.StockTransactionType = 32) AND (dbo.Product.ProductID = 32012)
)
Select StockTransactionDate, 
       ProductCode, 
       Description, 
       StockActual
From   Data
Where  RowId = 1

-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

 

[Cpreston]

Hi

That is great appears to be bringing in the right row now. It is really appreciated and I know it must be difficult without being able to test so it is very appreciated.
I will test with more data now.

Also I have never seen ROW_Number and Partition by so if you could kindly explain how this is working that would be great

Many thanks again

 

[gmmastros]

Consider this data:

TransactionDate         ProductCode          StockActual
----------------------- -------------------- ---------------------------------------
2015-06-15 07:25:00.000 ab123                543.000000
2015-01-25 13:31:00.000 ab123                234.000000
2014-07-10 14:30:00.000 ab123                789.400000

2016-01-25 13:31:00.000 mL019                336.000000
2014-07-10 14:30:00.000 mL019                950.400000
2014-05-22 07:31:00.000 mL019                268.800000
2013-03-15 07:25:00.000 mL019                624.000000

There are 2 separate product codes, if we add a row number to each row that reset with ProductCode changes and is ordered by TransactionDate DESC, the data would look like this:

TransactionDate         ProductCode          StockActual   RowNumber
----------------------- -------------------- ------------- --------------------
2015-06-15 07:25:00.000 ab123                543.000000    1
2015-01-25 13:31:00.000 ab123                234.000000    2
2014-07-10 14:30:00.000 ab123                789.400000    3

2016-01-25 13:31:00.000 mL019                336.000000    1
2014-07-10 14:30:00.000 mL019                950.400000    2
2014-05-22 07:31:00.000 mL019                268.800000    3
2013-03-15 07:25:00.000 mL019                624.000000    4

Notice how the newest transaction date for each product code has a RowNumber = 1. If we apply a filter for the data where RowNumber is 1, we end up with this.

TransactionDate         ProductCode          StockActual   RowNumber
----------------------- -------------------- ------------- --------------------
2015-06-15 07:25:00.000 ab123                543.000000    1
2016-01-25 13:31:00.000 mL019                336.000000    1

Row_Number is a way to assign a sequential number to all the rows in the query.
The over clause specifies how the row numbering should be done.
Partition By will cause the row numbering to reset to 1.
Order by identifies which rows in the group should be numbered 1, 2, 3, etc....

Row_Number() Over (
   Partition By dbo.Product.ProductCode 
   Order By dbo.StockTransaction.StockTransactionDate DESC
   ) As RowId

In this specific case, row numbering will reset to 1 every time Product Code changes.
for each product code, the row with the one will be the one with the newest StockTransactionDate, the next newest will be 2, and so on.

Now... copy this code to a sql server management studio query window.

Declare @Temp Table(TransactionDate DateTime, ProductCode VarChar(20), StockActual Decimal(20,6))

Insert Into @Temp Values('2014-05-22 07:31:00', 'mL019', 268.8)
Insert Into @Temp Values('2016-01-25 13:31:00', 'mL019', 336.0)
Insert Into @Temp Values('2013-03-15 07:25:00', 'mL019', 624.0)
Insert Into @Temp Values('2014-07-10 14:30:00', 'mL019', 950.4)

Insert Into @Temp Values('2015-01-25 13:31:00', 'ab123', 234.0)
Insert Into @Temp Values('2015-06-15 07:25:00', 'ab123', 543.0)
Insert Into @Temp Values('2014-07-10 14:30:00', 'ab123', 789.4)

[green]-- show the raw data[/green]
Select * From @Temp Order By ProductCode, TransactionDate DESC

[green]-- Add a row number column that resets with product code 
-- and has most recent Transaction date numbered 1[/green]
Select *,
       Row_Number() Over (Partition By ProductCode Order By TransactionDate DESC) As RowNumber
From   @Temp

[green]-- Apply filter for RowNumber = 1[/green]
; With Data As
(
  Select *,
         Row_Number() Over (Partition By ProductCode Order By TransactionDate DESC) As RowNumber
  From   @Temp
)
Select *
From   Data
Where  RowNumber = 1

Unfortunately, applying the filter requires an extra step because you cannot filter on Row_Number() so I used a common table expression to accomplish this.

I hope this makes sense to you. If there's any part that is confusing, 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