Avatar of blossompark
blossompark
Flag 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
 beforeAfter
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
blossompark

8/22/2022 - Mon
SOLUTION
dqmq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
JestersGrind

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
blossompark

ASKER
Hi...
thanks for all the responses....going to implement now and get back to you
blossompark

ASKER
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...

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Patrick Matthews

blossompark,

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

Patrick
blossompark

ASKER
thanks Patrick....just at  the omplementation stage of this...will update when finished...thanks again
blossompark

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
blossompark

ASKER
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