How to fixed this any one reply this [Resolved]

Posted by Jayakumars under .NET Framework on 3/3/2016 | Points: 10 | Views : 230 | Status : [Member] [MVP] | Replies : 1
Hi

CREATE TABLE TEMPPRODUCTS
(
ID int primary key identity(1,1),
ProductID int,
ProductName varchar(40),
BatchID int,
BatchNo int,
sizeid int,
sizeno int
)

INSERT INTO TEMPPRODUCTS VALUES(100,1,'Mouse',40,0,0,0)
INSERT INTO TEMPPRODUCTS VALUES(102,1,'Mouse',40,1,2,1)
INSERT INTO TEMPPRODUCTS VALUES(102,1,'Optical Mouse',40,2,1,3)
INSERT INTO TEMPPRODUCTS VALUES(100,1,'PenDrive',40,11,21,11)


-- I need Grouped ProductID Wise
--I need listed 2 records only grouped like tis

100,1,'Mouse',40,0,0,0
102,1,'Mouse',40,1,2,1

Mark as Answer if its helpful to you


Responses

Posted by: Professionaluser on: 3/4/2016 [Member] [MVP] Bronze | Points: 50

Up
0
Down

Resolved
on what criteria, u need the above result?

u try below query and check
SELECT t.ID, t.ProductID, t.ProductName, t.BatchID, t.BatchNo, t.sizeid, t.sizeno
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY BatchID) RN FROM TEMPPRODUCTS) t
WHERE RN=1



here am considering GROUP BY column as ID. To result the records as per your expected output, i assumed BatchID for sorting operation.


revert me back if you are not able to understand and above query is not suiting for your requirement

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response