query in different cases

[jacob21]

Hi,
I have a table: user
uid   status
1        1
2        1
3        1
4        2
5        2
6        1
7        2
8        1
9        3
10       2

I am trying to fetch data for below logic:
1) fetch top 6 rows having status=1
2) if status=1 has less then 6 rows then fetch remaining status 2 rows
   for example:if we have only 4 rows having status 1 then remaining two rows will be having status=2 rows(rows count should be 6)
   3)if status 1 has two rows only,and status=2 rows also,then remaining 2 rows will be having status=3
   for example: if we have 1 row having status 1 and 3 rows having status=2 ,then te remaining rows should have status 3 (count of rows should be 6)


   Need Suggestions...

[pritaeas]

Use order by status and limit 6

[jacob21]

Thanks for reply.
order is not fixed.for example
if status 1 has less then 6 rows ,next rows can be of status 3 or status 4

[pritaeas]

So. What query did you try?

[jacob21]

I don't have any idea..how to do..

using count

[pritaeas]

SELECT uid, status
FROM user
ORDER BY status, uid
LIMIT 6

[jacob21]

Hi,below query gives some ids having high priority...if it has less then 6 rows..another 
query execute which will fill remaining rows.(total rows 6)
Need suggestions

select  complaint3.id,ccs.ak_priority
from company, complaint3, complaint3_pricing_applied,complaint_current_status ccs
where
complaint3.id=complaint3_pricing_applied.complaint_id 
and ccs.complaint_id=complaint3.id 
and complaint3.company_id=company.id
and ccs.ak_priority=1
and complaint3_pricing_applied.is_active=1
and complaint3_pricing_applied.payment_pricing_id is not null
and complaint3.created_at>='2013-08-01' AND complaint3.created_at<='2014-01-08'  and  company.id=154 
order by complaint3.created_at DESC LIMIT 6

[pritaeas]

Give some sample data and show what you need as a result. I guess you need to remove the priority constraint and use it in the order by.

[jacob21]

I have 2 queries:
On execution of first Query it gives some ids.if count(id)>=6 ,only 1st query execute.
if(count(id))<6 then 1st and 2nd query execute..

total rows should be 6.if 1st query has less then 6 rows then 2nd query fill remaining rows.

Query 1:
select  count(complaint3.id) as paidStarredComplaints
from company, complaint3, complaint3_pricing_applied,complaint_current_status ccs
where
complaint3.id=complaint3_pricing_applied.complaint_id 
and ccs.complaint_id=complaint3.id 
and complaint3.company_id=company.id
and ccs.ak_priority=1
and complaint3_pricing_applied.is_active=1
and complaint3_pricing_applied.payment_pricing_id is not null
and complaint3.created_at>='2013-08-01' AND complaint3.created_at<='2014-01-08'  and  company.id=154 
order by complaint3.created_at DESC LIMIT 6

Query:2

select  count(complaint3.id) as UnPaidStarredComplaints
from company, complaint3,complaint_current_status ccs
where
complaint3.id=complaint3_pricing_applied.complaint_id 
and ccs.complaint_id=complaint3.id 
and complaint3.company_id=company.id
and ccs.ak_priority=1
and complaint3.created_at>='2013-08-01' AND complaint3.created_at<='2014-01-08'  and  company.id=154 
order by complaint3.created_at DESC LIMIT 6

[pritaeas]

Do a UNION of both queries then, and limit the result again to 6.