Thom A - Wednesday, December 6, 2017 5:23 AM

saravanatn - Wednesday, December 6, 2017 4:49 AM

I am just giving(test data just) an example that (ID=4 )records should be returned even if all the non primary keys columns are null. Because count=1 for ID=4 ....Regards,
Saravanan

The reason for our questions, however, is that your test data doesn't provide enough scenarios. As a result, we need to ask for more, or provide test data for you to tell us what you expect.

At a pure guess, maybe this is what you're after:

USE Sandbox;

GO

CREATE TABLE details (

  id int NOT NULL,

  [name] varchar(100),

  city varchar(100),

  place varchar(100));

--Sample Data: (comments start with a double hyphen)

INSERT INTO details

VALUES (1, 'sara', 'chennai', 't.nagar'),

   (1, NULL, NULL, NULL),

   (1, 'sara', 'chennai', 't.nagar'),

   (2, 'sara', 'chennai', 't.nagar'),

   (2, NULL, NULL, NULL),

   (3, 'sara', 'chennai', 't.nagar'),

   (4, NULL, NULL, NULL),

   (5, 'raj', 'vellore', 'kodambakkam'),

   (5, 'raj', 'vellore', NULL),

   (6, NULL, 'London','Kensington'),

   (6,'Steve', 'London',NULL),

   (7,'Steve', 'London','Kensington'),

   (7,'Steve', 'London','Barnet');

GO

SELECT d.id,

   MAX([name]) AS [name],

   MAX(city) AS city,

   MAX(place) AS place

FROM details d

GROUP BY d.id;

GO

DROP TABLE details;

GO

But, like I said, that's a guess. It doesn't do a logic your specified for ID 6 or 7 (which John and I have asked you about), so if this isn't correct you'll need to let us know what you expect the output to be.