greater record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

greater record

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2015-01-12 : 12:20:26

Hi, I have a view on sql 2008 that shows me all the lot numbers of each item, the problem is that some items has two lot numbers and I just need the lot number with the greater id number How can I do this?

Example :

ItemCode | Description | LotNumber | ID |
8-IRROMP | 8 IRROMPIBLE| 29350 |2678543|
8-IRROMP | 8 IRROMPIBLE| 29350 |2678623|
8-IRROMP | 8 IRROMPIBLE| 29355 |2720288|
9-IRROMP | 9 IRROMPIBLE| 29360 |2723021|

And I Need this :

ItemCode | Description | LotNumber | ID |
8-IRROMP | 8 IRROMPIBLE| 29350 |2678623|
8-IRROMP | 8 IRROMPIBLE| 29355 |2720288|
9-IRROMP | 9 IRROMPIBLE| 29360 |2723021|

This is my code :

SELECT     TOP (100) PERCENT dbo.VK_INVENTARIO.ItemCode, dbo.VK_INVENTARIO.Description, dbo.VK_INVENTARIO.LotNumber, dbo.VK_INVENTARIO.QtyAvailable, 
                      dbo.VK_INVENTARIO.Status, dbo.VK_INVENTARIO.Location, dbo.View_VK_LOT_PRICES.PurchasePrice, dbo.View_VK_LOT_PRICES.CUR, 
                      dbo.View_VK_LOT_PRICES.ID
FROM         dbo.VK_INVENTARIO LEFT OUTER JOIN
                      dbo.View_VK_LOT_PRICES ON dbo.VK_INVENTARIO.LotNumber = dbo.View_VK_LOT_PRICES.LotNumber AND 
                      dbo.VK_INVENTARIO.ItemCode = dbo.View_VK_LOT_PRICES.ItemCode
WHERE     (dbo.VK_INVENTARIO.ItemCode = '8-IRROMPIBLE')
ORDER BY dbo.VK_INVENTARIO.ItemCode

Thanks in advance for your help

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-12 : 13:39:58

In general:

select ItemCode, Description, LotNumber, max(id) ID
from ...
group by ItemCode, Descrption, LotNumber

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2015-01-13 : 10:45:26

I followed your suggestions but I discovered a problem, every record has different prices so I'still receiving the same records and I need to show that prices but only the price with the higer id of every lot.

Thanks Gbritton

ItemCode | Description | LotNumber | ID | PRICE |
8-IRROMP | 8 IRROMPIBLE| 29350 |2678543| 10.50 |
8-IRROMP | 8 IRROMPIBLE| 29350 |2678623| 10.55 |
8-IRROMP | 8 IRROMPIBLE| 29355 |2720288| 11.15 |
9-IRROMP | 9 IRROMPIBLE| 29360 |2723021| 12.35 |

I need it this way :

ItemCode | Description | LotNumber | ID | PRICE |
8-IRROMP | 8 IRROMPIBLE| 29350 |2678623| 10.55 |
8-IRROMP | 8 IRROMPIBLE| 29355 |2720288| 11.15 |
9-IRROMP | 9 IRROMPIBLE| 29360 |2723021| 12.35 |

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2015-01-13 : 13:32:10

[code]select
ItemCode,
Description,
LotNumber,
QtyAvailable,
Status,
Location,
PurchasePrice,
CUR,
ID
from
(

SELECT Row_Number() over (partition by dbo.VK_INVENTARIO.ItemCode, dbo.VK_INVENTARIO.Description, dbo.VK_INVENTARIO.LotNumber
order by dbo.View_VK_LOT_PRICES.ID DESC) as Rnum,
dbo.VK_INVENTARIO.ItemCode, dbo.VK_INVENTARIO.Description, dbo.VK_INVENTARIO.LotNumber, dbo.VK_INVENTARIO.QtyAvailable,
dbo.VK_INVENTARIO.Status, dbo.VK_INVENTARIO.Location, dbo.View_VK_LOT_PRICES.PurchasePrice, dbo.View_VK_LOT_PRICES.CUR,
dbo.View_VK_LOT_PRICES.ID
FROM dbo.VK_INVENTARIO LEFT OUTER JOIN
dbo.View_VK_LOT_PRICES ON dbo.VK_INVENTARIO.LotNumber = dbo.View_VK_LOT_PRICES.LotNumber AND
dbo.VK_INVENTARIO.ItemCode = dbo.View_VK_LOT_PRICES.ItemCode
WHERE (dbo.VK_INVENTARIO.ItemCode = '8-IRROMPIBLE')
) as dt
where Rnum = 1

[/code]

Too old to Rock'n'Roll too young to die.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-13 : 13:48:19

Simpler:


declare @t table (ItemCode varchar(20) ,Description varchar(20) , LotNumber int , ID int , PRICE money)
insert into @t (ItemCode , Description , LotNumber , ID , PRICE ) values
--ItemCode | Description | LotNumber | ID | PRICE |
('8-IRROMP','8 IRROMPIBLE',29350 ,2678543, 10.50 ),
('8-IRROMP','8 IRROMPIBLE',29350 ,2678623, 10.55 ),
('8-IRROMP','8 IRROMPIBLE',29355 ,2720288, 11.15 ),
('9-IRROMP','9 IRROMPIBLE',29360 ,2723021, 12.35 )

 	
select ItemCode, Description, LotNumber, id, Price
from @t t1
cross apply (
	select max(id) max_id from @t t2
	where t1.ItemCode = t2.ItemCode 
	  and t1.LotNumber = t2.LotNumber
	group by ItemCode, LotNumber) t2
where id = t2.max_id
order by ItemCode, ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources