sql - MySQL Query 2 records at top followed by the rest -

i'm stuck following problem.

i have website example supermarket shopping items. people can search website items. want on search result page @ top 2 items displayed have selected on offer. there can lots more items on offer.

so example, search shampoo, query display shampoo items in database table want 2 shampoo offer items @ top of query. there 2 or more shampoo offers in database table, other not shown.

example names :

table:

    id      name      c      d    ----------------------------------     1       jack      1      1            2       joe       1      1      3       dave      3      0      4       sue       1      0     5       mike      1      1     6       steve     4      0     7       david     1      0      8       susan     4      1      9       marc      1      1     10      ronald    4      1     11      michael   4      1   

example 1

query : c = 1 , d = 1 (but maximum of 2 'd' records, these 2 'd' records show @ top of result)

desired query result :

    id      name      c      d    ----------------------------------     1       jack      1      1            2       joe       1      1       4       sue       1      0     7       david     1      0   

example 2

query : c = 4 , d = 1 (but maximum of 2 'd' records, these 2 'd' records show @ top of result)

desired query result :

    id      name      c      d    ----------------------------------     8       susan     4      1      10      ronald    4      1           6       steve     4      0   

i hope explains goal i'm trying achieve.

many or suggestions!

that's 2 queries can combine union all:

select * mytable c = 1 , d = 1 limit 2 union select * mytable c = 1 , d = 0 order d desc; 

update: if want have 2 rows chosen randomly, order rand(). (without order rows chosen arbitrarily, means it's not guaranteed same 2 rows picked again when re-running query ‐ it's quite likely.) need order partial query (the first query in complete union-alled query), must use parentheses, because otherwise 1 order allowed, namely complete query @ query's end.

(select * mytable c = 1 , d = 1 order rand() limit 2) union (select * mytable c = 1 , d = 0) order d desc; 

sql fiddle: http://sqlfiddle.com/#!9/ed53f6/3.