SQL: SELECT n% with pictures, (100-n)% without pictures

Question

Hi all,

we have a DB which stores users who may have pictures.

I am looking for an elegant way in SQL to get the following results: Select n users. Of those n users e.g. 60% should have an associated picture and 40% should not have a picture. If there are less than 60% users having a picture the result should be filled up with users wihtout an image.

Is there some elegant way in SQL without firing multiple SELECTs to the DB?

Thank you very much.

Answer 1

SELECT TOP(n) HasPicture --should be 0 or 1 to allow ORDER
   FROM Users
   ORDER BY 1

Answer 2

Ugly code:

SELECT TOP @n * FROM
(


      //-- We start selecting users who have a picture (ordered by HasPicture)
      //-- If there is no more users with a picture, this query will fill the 
      //-- remaining rows with users without a picture
      SELECT TOP 60 PERCENT * FROM tbUser
      ORDER BY HasPicture DESC

      UNION

      //-- This is to make sure that we select at least 40% users without a picture
      //-- AT LEAST because in the first query it is possible that users without a 
      //-- picture have been selected
      SELECT TOP 40 PERCENT * FROM tblUser
      WHERE HasPicture = 0

      //-- We need to avoid duplicates because in the first select query we haven't 
      //-- specified HasPicture = 1 (and we didn't want to).
      AND UserID not IN
      (
        SELECT TOP 60 PERCENT UserID FROM tbUser
        ORDER BY HavePicture DESC
      )
 )

Answer 3

So you provide @n, being the number of users you want. You provide @x being the percentage of those users who should have pictures.

select top (@n) *
from
(
select top (@n * @x / 100) *
from users
where picture is not null 

union all

select top (@n) *
from users
where picture is null 
) u
order by case when picture is not null then 1 else 2 end;

So... you want at most @n * @x / 100 users who have pictures, and the rest have to be people who don't have pictures. So I'm doing a 'union all' between my @n*@x/100 picture-people and enough others to complete my @n. Then I'm selecting them back, ordering my TOP to make sure that I keep the people who have a picture.

Rob

Edited: Actually, this would be better:

select top (@n) *
from
(
select top (@n * @x / 100) *, 0 as NoPicture
from users
where picture is not null 

union all

select top (@n) *, 1 as NoPicture
from users
where picture is null 
) u
order by NoPicture;

...because it removes the impact of the ORDER BY.

Answer 4

Use the Select case for this type of Requirement.