Link to home
Create AccountLog in
Avatar of bapkins
bapkins

asked on

SQL Extract Last Buy Price by max tranref

Hi Guys,
I have a table from which i need to extract the last buyprice for each stockcode.

The last buy price will be the highest tranref number.

The table is:

dbo.PASSSTKBUYPRICES

That table looks like the attached spreadsheet. I would like a query which will select the row with the highest tranref for each stockcode and display that price.

I have tried to explain what i am looking for on sheet 2 of the attached excel file.

If you guys could give me an sql query that would be great. Book3.xlsx
Avatar of mds-cos
mds-cos
Flag of United States of America image

OK, so I'm not going to open up your attached files (yes, call me paranoid...sorry), but here is a generic approach based on my understanding of your question:

SELECT TOP 1 stockcode, buyprice
FROM passstkbuyprices
ORDER BY tranref DESC
PS -- if you want to re-attach your files as a PDF or generic .txt document I'll be happy to look at them!  But I'm sure the query above is what you are looking for.
Avatar of bapkins
bapkins

ASKER

Hi Guys,
That query only gave me 1 product ?

There are lots of products in the list and i need every one of them but only once.

It is in the excel file which i have re-attached as a 2007 excel file - trust me theres no viruses in it !

Cheers

Barry Book3.xls
Avatar of rcombis
Try something like this:

 Select dbo.PASSSTKBUYPRICES.itemref, stkCode, unitCost
FROM (SELECT MAX(itemref) as itemRef
FROM dbo.PASSSTKBUYPRICES
group by stkcode) as temp1, dbo.Table_1
wheredbo.PASSSTKBUYPRICES.itemref=temp1.itemRef
Avatar of bapkins

ASKER

Hi,
I get the following error when i try to create the view

Msg 208, Level 16, State 1, Procedure PASS_StkLastBuyPrice, Line 5
Invalid object name 'dbo.Table_1'.

This is the query:

create view PASS_StkLastBuyPrice as
 Select dbo.PASSSTKBUYPRICES.itemref, stkCode, unitCost
FROM (SELECT MAX(itemref) as itemRef
FROM dbo.PASSSTKBUYPRICES
group by stkcode) as temp1, dbo.Table_1
where dbo.PASSSTKBUYPRICES.itemref=temp1.itemRef
Select dbo.PASSSTKBUYPRICES.itemref, stkCode, unitCost
FROM (SELECT MAX(itemref) as itemRef
FROM dbo.PASSSTKBUYPRICES
group by stkcode) as temp1, dbo.PASSSTKBUYPRICES
where dbo.PASSSTKBUYPRICES.itemref=temp1.itemRef
      
Above is the code.  I had called the table dbo.Table_1 in my tests.
Yup, that is exactly what it would return.  Sorry, I was thinking along the lines of pulling a single item....but of course you need the full list.

create view PASS_StkLastBuyPrice as
 Select t2.itemref, t2.stkCode, t2.unitCost
FROM (SELECT MAX(itemref) as itemRef
FROM dbo.PASSSTKBUYPRICES
group by stkcode) as t1 inner join  PASSSTKBUYPRICES t2
where t1.itemref=t2.itemRef
ASKER CERTIFIED SOLUTION
Avatar of p_nuts
p_nuts
Flag of Netherlands image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SELECT * 
  FROM PASSSTKBUYPRICES t1 
 WHERE t1.itemref = (SELECT MAX(t2.itemref) 
                       FROM PASSSTKBUYPRICES t2 
                      WHERE t1.stkcode = t2.stkcode)

Open in new window

@sharath_123 that will only show 1 IT needs to be grouped
>> @sharath_123 that will only show 1 IT needs to be grouped

Did you execute my query? Here is the result with my query:

declare @PASSSTKBUYPRICES table (ITEMREF int,STKCODE varchar(100),UNITCOST numeric(10,2))
insert @PASSSTKBUYPRICES values (189,	'MEGMT2',	499.2)
insert @PASSSTKBUYPRICES values (191,	'MEGMT2',	0)
insert @PASSSTKBUYPRICES values (193,	'PASSMISC',	0)
insert @PASSSTKBUYPRICES values (196,	'FLUMT3',	565.95)
insert @PASSSTKBUYPRICES values (197,	'SEAPT1',	142.2)
insert @PASSSTKBUYPRICES values (1398,	'SEAPT9',	647.35)
insert @PASSSTKBUYPRICES values (1399,	'SEAPT9',	647.35)

SELECT * 
  FROM @PASSSTKBUYPRICES t1 
 WHERE t1.itemref = (SELECT MAX(t2.itemref) 
                       FROM @PASSSTKBUYPRICES t2 
                      WHERE t1.stkcode = t2.stkcode)
 ORDER BY ITEMREF
/*
ITEMREF	STKCODE	UNITCOST
191	MEGMT2	0.00
193	PASSMISC	0.00
196	FLUMT3	565.95
197	SEAPT1	142.20
1399	SEAPT9	647.35
*/

Open in new window