May 8, 2014 at 6:00 pm
Hi,
Below are my table structure
create table Test (ProductId int primary key identity(1,1), productnumber varchar(50),productname varchar(50),Quantity int)
insert into Test values('PN1001','Pepsi',20),
('PN1001','curd',2),
('PN1001','Noodles',10),
('PN1001','Shrimp',40),
('PN1002','Mango',10),
('PN1002','Pizza',5),
('PN1002','Soup',10),
('PN1002','Honey',30),
('PN1001','Pepsi',15),
('PN1001','Curd',50),
('PN1001','Noodles',13),
('PN1001','Shrimp',32),
('PN1002','Mango',11),
('PN1002','Pizza',14),
('PN1002','Soup',18),
('PN1002','Honey',20)
I need to take the last inserted set of records. unfortunately i don't have created date in my table and not sure why it hasn't created when the guy build the table schema. i know this is poor table structure cannot do anything at the moment.
Me required output :
select 'PN1001' as productnumber,'Pepsi' as productname,15 as Quantity union all
select 'PN1001' as productnumber,'curd' as productname,50 as Quantity union all
select 'PN1001' as productnumber,'Noodles' as productname,13 as Quantity union all
select 'PN1001' as productnumber,'Shrimp' as productname,32 as Quantity union all
select 'PN1002' as productnumber,'Mango' as productname,11 as Quantity union all
select 'PN1002' as productnumber,'Pizza' as productname,14 as Quantity union all
select 'PN1002' as productnumber,'Soup' as productname,18 as Quantity union all
select 'PN1004' as productnumber,'Honey' as productname,20 as Quantity
ie. The logic i need to achieve is to get the last insert records based on productnumber.
This is my try
;with TotalRecords as (
select ROW_NUMBER() OVER
(PARTITION BY productnumber
Order BY productnumber DESC )
AS RowNumber,productnumber, productname,Quantity from Test)
select * from TotalRecords
please Note : on this sample i can specify Rownumber = 3 to get latest records. but i should not do that because i don't need to specify the particular number as it can change any time. so please help me achieving my requiement
Any sample query please
May 8, 2014 at 7:31 pm
this is how i achieved.
;
WITH TotalRecords
AS (
SELECT ROW_NUMBER() OVER (
PARTITION BY productnumber
,productname ORDER BY productid DESC
) AS RowNumber
,productnumber
,productname
,Quantity
FROM Test
)
SELECT *
FROM TotalRecords
WHERE rownumber = 1
May 9, 2014 at 5:34 am
I'm not sure this will always give you what you want. It may not even give the same answer on subsequent runs. The reason is that your ORDER BY excludes productname or Quantity. So, the order will be by productid DESC alone. That is, the order within a given productid is essentially random.
It may work on small samples but not on large tables
May 9, 2014 at 6:10 am
gbritton1 (5/9/2014)
I'm not sure this will always give you what you want. It may not even give the same answer on subsequent runs. The reason is that your ORDER BY excludes productname or Quantity. So, the order will be by productid DESC alone. That is, the order within a given productid is essentially random.It may work on small samples but not on large tables
ProductID is unique in the DDL provided.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply