1

The scenario is that I have a table that looks like this, with book IDs, their titles in English and/or French, and their sales. There are also some nulls. Some books will have only English or only French titles. Some may have multiple of both or either.

ID Title Language Sales
12345 Sorceror's Stone English 50,000,000
12345 Philosopher's Stone English 50,000,000
12345 A L'ecole de sorcier French 50,000,000
33333 NULL NULL NULL
67890 A Christmas Carol English 65,000,000
67890 Un Chant de Noel French 65,000,000
24680 La Fascination French 30,000,000
24680 La Crépuscule French 30,000,000
13579 NULL NULL NULL

Basically, I want to narrow down this table as follows:

  1. If at least 1 English title exists, we want the alphabetically first English title.
  2. Else if at least 1 French title exists, we want the alphabetically first French title.
  3. Else, just have string null.

I want my resultant table to look like:

ID Title Language Sales
12345 Philosopher's Stone English 50,000,000
33333 null null null
67890 A Christmas Carol English 65,000,000
24680 La Crépuscule French 30,000,000
13579 null null null
Dale K
  • 25,246
  • 15
  • 42
  • 71
W1010
  • 27
  • 1
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Charlieface Nov 28 '21 at 09:56

1 Answers1

1

Using row_number analytic window function:

with title as (
    select * , 
        Row_Number() over(partition by id order by case when [Language]='English' then 0 else 1 end, title) rn
    from t
)
select id, title, [language], Sales
from title
where rn=1
Stu
  • 30,392
  • 6
  • 14
  • 33
  • 1
    Windowing functions are almost always the answer to this kind of "arbitrarily de-duplicate records" problem, and some of the others like rank, dense rank, etc. are sometimes more useful. For Asker's reference the "with * as" syntax is a CTE, and also usually needed in these cases since you sometimes must apply windowing or grouping on results. It can be accomplished with subqueries but using CTEs is much easier to read, and also you can run the CTE by itself to debug or understand each step of the query. – AaronLS Nov 27 '21 at 22:52