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 Caption2 00000002 Front cover2 00000002 Back cover118 00000118 Front cover118 00000118 Back cover130 00000130 FRONT COVER130 00000130 BACK COVER130 00000130 P2130 00000130 P6202 00000202 Front cover202 00000202 Back cover202 00000202 Preface202 00000202 Color page 1202 00000202 Color page 2220 00000220 Front Cover220 00000220 Table of Contents220 00000220 Introduction220 00000220 Sample Page 1220 00000220 Sample Page 2220 00000220 Back Cover284 00000284 Front cover284 00000284 Back cover290 00000290 FRONT COVER290 00000290 BACK COVER290 00000290 MUSIC SAMPLE384 00000384 Front Cover384 00000384 Back Cover384 00000384 Contents384 00000384 Performer credits385 00000385 Front Cover385 00000385 Back Cover
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;
Wayne Barron
ASKER
@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
Wayne Barron
ASKER
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.
If the ItemNo and CloserLookId are the same, you can't guarantee which row you get back except by ordering on the Caption.