troubleshooting Question

Get Distinct TOP record from multiple columns

Avatar of Wayne Barron
Wayne BarronFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
7 Comments1 Solution97 ViewsLast Modified:
Hello, all;
(SQL Server 2016)

I need to get the top record for all duplicate numbers.
When I try a distinct on the itemID, it gives me the bottom record and skips a lot of rows for some reason.
(And yes, I have also tried MIN and MAX on this as well, and it still does not work.)

Here is a sample of the data. I need to return the TOP rows, from all the duplicates.
As it is it Front cover.
I could do  
caption='Front Cover'
However, there are some entries that do not have a cover, and their entry is NULL.

ItemNo	CloserLookID	Caption
2	00000002	Front cover
2	00000002	Back cover
118	00000118	Front cover
118	00000118	Back cover
130	00000130	FRONT COVER
130	00000130	BACK COVER
130	00000130	P2
130	00000130	P6
202	00000202	Front cover
202	00000202	Back cover
202	00000202	Preface
202	00000202	Color page 1
202	00000202	Color page 2
220	00000220	Front Cover
220	00000220	Table of Contents
220	00000220	Introduction
220	00000220	Sample Page 1
220	00000220	Sample Page 2
220	00000220	Back Cover
284	00000284	Front cover
284	00000284	Back cover
290	00000290	FRONT COVER
290	00000290	BACK COVER
290	00000290	MUSIC SAMPLE
384	00000384	Front Cover
384	00000384	Back Cover
384	00000384	Contents
384	00000384	Performer credits
385	00000385	Front Cover
385	00000385	Back Cover

Wayne Barron
Author, Web Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2009

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros