Get Distinct TOP record from multiple columns

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

Open in new window


Thanks
Carrkiss
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
You need to define "TOP".  SQL has no direct idea of the "first" row that was inserted to the table.

If the ItemNo and CloserLookId are the same, you can't guarantee which row you get back except by ordering on the Caption.
0
ste5anSenior DeveloperCommented:
hmm, despite how you define your order for TOP, using ROWNUMBER() is a common approach:

DECLARE @Sample TABLE
    (
        ItemNo INT ,
        CloserLookID INT ,
        Caption NVARCHAR(255)
    );

INSERT INTO @Sample ( ItemNo ,
                      CloserLookID ,
                      Caption )
VALUES ( 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' );

WITH Ordered
AS ( SELECT * ,
            ROW_NUMBER() OVER ( PARTITION BY S.Caption
                                ORDER BY S.ItemNo ,
                                         S.CloserLookID ) AS RN
     FROM   @Sample S )
SELECT *
FROM   Ordered O
WHERE  O.RN = 1;

Open in new window

0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@Ste5an
Does not work.
This is what I get, running your SQL Example.

ItemNo	CloserLookID	Caption	RN
2	2	Back cover	1
202	202	Color page 1	1
202	202	Color page 2	1
384	384	Contents	1
2	2	Front cover	1
220	220	Introduction	1
290	290	MUSIC SAMPLE	1
130	130	P2	1
130	130	P6	1
384	384	Performer credits	1
202	202	Preface	1
220	220	Sample Page 1	1
220	220	Sample Page 2	1
220	220	Table of Contents	1

Open in new window


another choice would be.
To run this
Caption = 'Cover' or caption='Front Cover'

This seems to work, I will just have to go through the entire list of over 100,000 records to make sure all have either a
Front Cover (OR) Cover

It is a pain working with someone else's data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

ste5anSenior DeveloperCommented:
Well, what is your desired output from your sample data? It's not clear what you want to get.. post it.
0
awking00Information Technology SpecialistCommented:
I don't think this should be abandoned so quickly. Just a little more information is needed (expected results from the sample would be most helpful). Are you saying that a caption value of 'FrontCover', 'Cover',' Sample Page 1', or 'Box Front' should be deemed to be the "TOP"?
Perhaps something like the following would work.
with cte as
(select ItemNo, CloserLookID, Caption,
 case when lower(Caption) = 'front cover' or lower(Caption) = 'cover' or lower(Caption) = 'sample page 1' or lower(Caption) = 'box front' then 1
          else 2
 end orderno
 from sample),
cte2 as
(select ItemNo, CloserLookID, Caption,
 row_number() over (partition by ItemNo order by orderno) rowno
 from cte
 where orderno = 1)
select temNo, CloserLookID, Caption
from cte2
where rowno = 1;
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@awking00
Unfortunately, the data has the values in CAPTION all over the place.
(The design of this database reminds me of when I first started learning this stuff 18+years ago. It is a mess)
I could order it, but the data gets updates once a week, and that would be a little bit to much work.
Considering there are over 80+ thousand records that I have to update every single week, once this site goes live.

So, I am going to leave it at how I have it set right now.
Which is, I created a REPLACE script that runs through the table once I have it imported, and I change all the instances to 'front cover'
So, all I have to do now is simply run the script and grab just the front Cover, and I get all the data that is there for the query.

thanks for the added information, it is much appreciated.
Wayne
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
I will have to go with the choice I chose, as the Front Cover, seems to NOT always be at the top.
So, I have to look at
Caption= Front Cover, Cover, Sample Page 1, Box Front

So, it is a pain, but simply to work with, in order to get the results that are needed.

Thanks for the attempted assistance from both Experts.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.