Get Distinct TOP record from multiple columns

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.

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;

Select all Open in new window

Well, what is your desired output from your sample data? It's not clear what you want to get.. post it.

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;

@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

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.