CODEWITHSUNDEEP
sqlsql-serversql-server-ce
William Madonna Jr.
William Madonna Jr.

Reputation: 242

Need help converting SQL Server query to SQL Server Compact Edition

I have this query running against my SQL Server:

SELECT SongUID, ArtistAlias, TitleAlias, Quality 
FROM 
    (SELECT 
        ROW_NUMBER() OVER(PARTITION BY ArtistAlias, TitleAlias 
                          ORDER BY Quality Desc) AS rn, * 
     FROM KaraokeLibrary) dt
WHERE rn = 1

But I cannot get it to run under SQL Server CE 4 (ROW_NUMBER() not supported and OVER...PARTITION not supported).

I've been able to get "close" in SQL Server CE with this query:

SELECT SongUID, ArtistAlias, TitleAlias, Quality 
FROM KaraokeLibrary A
WHERE Quality >= 0 AND Quality IN (SELECT MAX(Quality) 
                                 FROM KaraokeLibrary B 
                                 WHERE B.ArtistAlias = A.ArtistAlias 
                                   AND B.TitleAlias = A.TitleAlias)
ORDER BY ArtistAlias, TitleAlias

But that still lists duplicate artist/titles - cannot have this.

Any SQL Server CE gurus have some query magic for me on this one?

UPDATE: To further explain – the final results I am after is when given data that looks like this:

SongUID  ArtistAlias  TitleAlias  Quality
    10,    'Artist1',    'Title1',    3
    11,    'Artist1',    'Title1',    2
    12,    'Artist1',    'Title1',    1
    13,    'Artist2',    'Title1',    3
    14,    'Artist2',    'Title1',    2
    15,    'Artist2',    'Title1',    1
    16,    'Artist2',    'Title2',    2
    18,    'Artist2',    'Title2',    1
    19,    'Artist2',    'Title2',    3
    20,    'Artist2',    'Title2',    3
    21,    'Artist2',    'Title2',    1

I would like to have this returned:

10, 'Artist1', 'Title1', 3
13, 'Artist2', 'Title1', 3
20, 'Artist2', 'Title2', 3

Upvotes: 0

Views: 513

Answers (2)

Taryn
Taryn

Reputation: 247720

Have you tried just using a subquery:

select k1.SongUID,
  k1.ArtistAlias, 
  k1.TitleAlias,
  k1.quality
from KaraokeLibrary k1
inner join 
(
  select max(quality) maxQuality,
    ArtistAlias, TitleAlias
  from KaraokeLibrary
  where  Quality >= 0
  group by ArtistAlias, TitleAlias
) k2
  on k1.ArtistAlias = k2.ArtistAlias
  and k1.TitleAlias = k2.TitleAlias
  and k1.quality = k2.maxQuality
where  k1.Quality >= 0

See SQL Fiddle with Demo

If you have multiple versions of the songuid, then you can use an aggregate:

select max(k1.SongUID),
  k1.ArtistAlias, 
  k1.TitleAlias,
  k1.quality
from KaraokeLibrary k1
inner join 
(
  select max(quality) maxQuality,
    ArtistAlias, TitleAlias
  from KaraokeLibrary
  where  Quality >= 0
  group by ArtistAlias, TitleAlias
) k2
  on k1.ArtistAlias = k2.ArtistAlias
  and k1.TitleAlias = k2.TitleAlias
  and k1.quality = k2.maxQuality
where  k1.Quality >= 0
group by k1.ArtistAlias, 
  k1.TitleAlias,
  k1.quality

See SQL Fiddle with Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270001

You can also write the query like this:

SELECT  SongUID, ArtistAlias, TitleAlias, Quality
From KaraokeLibrary kl
where quality > 0 and
      songuid = (select top ( 1 ) songuid
                 from Karokelibrary kl2
                 WHERE kl.ArtistAlias=kl2.ArtistAlias AND kl.TitleAlias=kl2.TitleAlias
                 order by quality desc
                )

(Note: you don't need the spaces in "top ( 1 )". It is the only way I could upload the solution.)

Older versions of SQL CE may not support top.

Upvotes: 1

Related Questions