Link to home
Start Free TrialLog in
Avatar of blossompark
blossomparkFlag for Ireland

asked on

selecting returning single rows of data where multiple rows exist

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
 User generated image
SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick Matthews
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

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of blossompark

ASKER

Hi...
thanks for all the responses....going to implement now and get back to you
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...

blossompark,

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

Patrick
thanks Patrick....just at  the omplementation stage of this...will update when finished...thanks again
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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