selecting returning single rows of data where multiple rows exist

blossompark
blossompark used Ask the Experts™
on
I have a table with about 50,000 rows.
I want  a query that returns one row for each NAME, based on which row has the least number of NULLs.
If  rows with the same NAME have equal number of NULLS I do not mind which row is returned as long as only one is..
Any help appreciated thanks
 beforeAfter
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
;with mycte as
(
select name, a, b,...j, row_number() over(partition by name order by cnt desc) as seq
from
(
select *
,(case when b is null then 1 else 0 end
+case when c is null then 1 else 0 end
...
+case when j is null then 1 else 0 end) cnt
from Yourtable
) as T
)
select name, a, b, .... j from myCTE where seq = 1
Top Expert 2010

Commented:
SELECT z.[Name], z.A, z.B, z.C, z.D, z.E, z.F, z.G, z.H, z.I, z.J
FROM
    (SELECT t1.[Name], t1.A, t1.B, t1.C, t1.D, t1.E, t1.F, t1.G, t1.H, t1.I, t1.J,
        ROW_NUMBER(PARTITION BY t1.[NAME] ORDER BY t1.B) AS RowNum
    FROM SomeTable t1) z
WHERE z.RowNum = 1
ORDER BY z.[Name]

Open in new window


;WITH Ranking
AS
(
SELECT  
	Name, B, C, D, E, F, G, H, I, J, 
	CASE WHEN B IS NULL THEN 0 ELSE 1 END +
	CASE WHEN C IS NULL THEN 0 ELSE 1 END +
	CASE WHEN D IS NULL THEN 0 ELSE 1 END +
	CASE WHEN E IS NULL THEN 0 ELSE 1 END +
	CASE WHEN F IS NULL THEN 0 ELSE 1 END +
	CASE WHEN G IS NULL THEN 0 ELSE 1 END +
	CASE WHEN H IS NULL THEN 0 ELSE 1 END +
	CASE WHEN I IS NULL THEN 0 ELSE 1 END +
	CASE WHEN J IS NULL THEN 0 ELSE 1 END AS Ranking
FROM   
	 YourTable
),
Ordering
AS
(
SELECT
	ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name, Ranking DESC) AS RowID,
	Name, B, C, D, E, F, G, H, I, J
FROM   
	Ranking
)
SELECT  
	Name, B, C, D, E, F, G, H, I, J
FROM   
	 Ordering
WHERE 
	RowID = 1

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Hi...
thanks for all the responses....going to implement now and get back to you

Author

Commented:
Hi all...
i have an ammendment to my original request which hopefully will make it easier...
instead of returning   a single row for each NAME with  the least number of NULLS.

I want it to return ANY single row for each  NAME...

Top Expert 2010

Commented:
blossompark,

My suggestion should do just that.  (I misunderstood your original request, but did inadvertently fulfill the amended request :)

Patrick

Author

Commented:
thanks Patrick....just at  the omplementation stage of this...will update when finished...thanks again

Author

Commented:
Hi Patrick,
returning an error while parsing
Incorrect syntax near the keyword 'BY'.

seems to be pointing to this line;
ROW_NUMBER(PARTITION BY t1.[NAME] ORDER BY t1.B) AS RowNum
Top Expert 2010
Commented:
Sorry, had some syntax errors and also a bad column name.

The following seems to work:

CREATE TABLE SomeTable ([Name] varchar(20), B int, C int, D int, 
    E int, F int, G int, H int, I int, J int)

INSERT INTO SomeTable ([Name], B, C, D, E, F, G, H, I, J)
SELECT 'ABC123', 1, 2, 1, NULL, 4, NULL, 5, 6, NULL UNION ALL
SELECT 'ABC123', 1, 2, NULL, NULL, NULL, NULL, NULL, 6, 5 UNION ALL
SELECT 'ABC234', 1, 2, 3, 4, 2, NULL, NULL, 2, 2 UNION ALL
SELECT 'ABC235', 1, 2, 3, 4, 5, 6, 7, 4, 1 UNION ALL
SELECT 'ABC235', 5, 3, 1, 4, 1, 6, 7, 4, NULL

SELECT z.[Name], z.B, z.C, z.D, z.E, z.F, z.G, z.H, z.I, z.J
FROM
    (SELECT t1.[Name], t1.B, t1.C, t1.D, t1.E, t1.F, t1.G, t1.H, t1.I, t1.J,
        ROW_NUMBER() OVER(PARTITION BY t1.[NAME] ORDER BY t1.B) AS RowNum
    FROM SomeTable t1) z
WHERE z.RowNum = 1
ORDER BY z.[Name]

DROP TABLE SomeTable

Open in new window



Its output:

ABC123	1	2	1	NULL	4	NULL	5	6	NULL
ABC234	1	2	3	4	2	NULL	NULL	2	2
ABC235	1	2	3	4	5	6	7	4	1

Open in new window


Author

Commented:
Hi dqmq...did not try your solution, thanks for the input..
Hi JesterGrind...yours worked fine  thankyou
Hi Patrick...havent tried your updated solution yet but i'm sure its perfect...
thanks everyone...:-)
made my day as usual

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial