myyis
asked on
Mysql get top rows
I want get ( the rows which are not the last 2000 rows) from the ones having more than 2000 rows.
How can I do?
SELECT (the ones which are not last 2000) FROM FEED WHERE ORID IN (
SELECT ORID
FROM `FEED`
GROUP BY ORID
HAVING COUNT( * ) >2000)
How can I do?
SELECT (the ones which are not last 2000) FROM FEED WHERE ORID IN (
SELECT ORID
FROM `FEED`
GROUP BY ORID
HAVING COUNT( * ) >2000)
ASKER
I think you query gives me the count (*). But I need the rows of Feed table itselves.
eg.
Lets say the FEED has fields ID (PK) and ORID,
it has
2500 records with ORID=1
1500 records with ORID=2
3700 records with ORID=3
I need to have the list of records
First 500 records with ORID=1 UNION First 1700 records with ORID=3
(Nothing from ORID=2 since #of records are less than 2000)
eg.
Lets say the FEED has fields ID (PK) and ORID,
it has
2500 records with ORID=1
1500 records with ORID=2
3700 records with ORID=3
I need to have the list of records
First 500 records with ORID=1 UNION First 1700 records with ORID=3
(Nothing from ORID=2 since #of records are less than 2000)
>> First 500 records with ORID=1 UNION First 1700 records with ORID=3
Do you have any date field like Update date, Modified date to order the records?
Do you have any date field like Update date, Modified date to order the records?
ASKER
No I don't have. The order with ID (PK) is sufficient.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lets say the FEED has fields ID (PK) and ORID,(500+1,700) = 2,200 :: why 2,200 records?
it has
2500 records with ORID=1
1500 records with ORID=2 << ignored
3700 records with ORID=3
I need to have the list of records
First 500 records with ORID=1 UNION First 1700 records with ORID=3
also:
why 500 of 2,500 ? (20% of that ordid)
why 1,700 of 3,700 ? (46% of that ordid)
@PortletPaul, he needs the records excluding the first 2000, he stated this in the original post. So if an ORID has < 2000 records it would not return any rows.
this may be possible but you would need better than the community version of mysql so that you can put a LIMIT into a subquery.
Example
create a view with this as the sql statement
then make a second view (get the last 2000 records)
then your query
I think this query would also take a very long time to run.
this may be possible but you would need better than the community version of mysql so that you can put a LIMIT into a subquery.
Example
create a view with this as the sql statement
select ORID, count(ORID) as feed_count from feed
group by ORID
then make a second view (get the last 2000 records)
Select f1.id from feed as f1 where 2000 <
(Select feed_count from name_of_your_view as f2 where f2.ORID = f1.ORID)
order by ID desc
then your query
Select * from feed f1 where ID in (select id from name_of_second_view f2 where f1.ORID = f2.ORID LIMIT 2000,18446744073709551615)
order by ORID asc
I think this query would also take a very long time to run.
ASKER
worked! great!
SELECT * FROM (
SELECT ORID,COUNT(*) cnt
FROM `FEED`
GROUP BY ORID
HAVING COUNT( * ) >2000) t1
ORDER BY cnt DESC
LIMIT 2000