Query to display unique comments based on certain conditions
-
TABLE DEFINITION
Column NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)
CUSTOMER TABLE
CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
OUTPUT
CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
Records should be filtered by
1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo
-
souravagar (7/1/2015)
TABLE DEFINITIONColumn NameComments
CustomerIDINT
SequenceNoINT
CommentsVARCHAR(MAX)
CUSTOMER TABLE
CustomerIDSequenceNoComments
11ABC D
12CDE
13ABC
14ABC D
15CDE
16abc
27ABC DEF
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
OUTPUT
CustomerIDSequenceNoComments
13ABC
14ABC D
15CDE
16abc
28
29ABC DEF
210DEF
211XYZ 123
212ABC
313PQ RST
Records should be filtered by
1.Display only Unique Comments from Customer Table for all the customers,
2.If Comments are same then display the row which has maximum SequenceNo
Here you go:
DECLARE @CUSTOMER AS TABLE (
CustomerID INT,
SequenceNo INT,
Comments VARCHAR(MAX)
);
INSERT INTO @CUSTOMER (CustomerID, SequenceNo, Comments)
VALUES (1, 1, 'ABC D'),
(1, 2, 'CDE'),
(1, 3, 'ABC'),
(1, 4, 'ABC D'),
(1, 5, 'CDE'),
(1, 6, 'abc'),
(2, 7, 'ABC DEF'),
(2, 8, ''),
(2, 9, 'ABC DEF'),
(2, 10, 'DEF'),
(2, 11, 'XYZ 123'),
(2, 12, 'ABC'),
(3, 13, 'PQ RST');
SELECT C.CustomerID, C.Comments, MAX(C.SequenceNo) AS SequenceNo
FROM @CUSTOMER AS C
GROUP BY C.CustomerID, C.Comments
ORDER BY C.CustomerID, MAX(C.SequenceNo)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Thank you.:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply