INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Only require one row based on MAX

Only require one row based on MAX

(OP)
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.

CODE --> SQL

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


CODE --> SQL

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 

RE: Only require one row based on MAX

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.

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

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

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

Try:

WHERE (dbo.StockTransaction.StockTransactionType = 32)
AND (dbo.Product.ProductID = 32012)
AND (dbo.StockTransaction.StockActual = '624.0000')


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.

RE: Only require one row based on MAX

(OP)
Hi
And in this case it is not what I want dazed

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

RE: Only require one row based on MAX

try this:

CODE

; 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

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

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

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

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.

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

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

CODE

; 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

RE: Only require one row based on MAX

OK, then sorry for bothering.

RE: Only require one row based on MAX

(OP)
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.

RE: Only require one row based on MAX

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

try ordering StockTransactionDate DESC. Like this:

CODE

; 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

RE: Only require one row based on MAX

(OP)
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

RE: Only require one row based on MAX

Consider this data:

CODE

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:

CODE

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.

CODE

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....


CODE

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.

CODE

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)

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

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

-- Apply filter for RowNumber = 1
; 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close