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)
LVL 1
myyisAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
Can you try this?
SELECT ORDID, 
       ID 
  FROM (SELECT *, 
               IF(@ORID = ORID, @rownum := @rownum + 1, @rownum := 1) AS RowNum, 
               IF(@ORID <> ORID, @ORID := ORID, -999)                 s 
          FROM (SELECT * 
                  FROM FEED 
                 WHERE ORID IN (SELECT ORID 
                                  FROM FEED 
                                 GROUP BY ORID 
                                HAVING COUNT(*) > 2000)) t1, 
               (SELECT @rownum := 1, 
                       @ORID := -999) r 
         ORDER BY ORID, 
                  ID DESC) t2 
 WHERE RowNum > 2000 
 ORDER BY ORID, 
          ID;

Open in new window

0
 
SharathData EngineerCommented:
Are you looking for this?
SELECT * FROM (
SELECT ORID,COUNT(*) cnt
FROM  `FEED`
GROUP BY ORID
HAVING COUNT( * ) >2000) t1
ORDER BY cnt DESC
LIMIT 2000
0
 
myyisAuthor Commented:
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)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SharathData EngineerCommented:
>> 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?
0
 
myyisAuthor Commented:
No I don't have.  The order with  ID (PK) is sufficient.
0
 
PortletPaulfreelancerCommented:
Lets say the FEED has fields  ID (PK)  and ORID,
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
(500+1,700) = 2,200 :: why 2,200 records?

also:
why 500 of 2,500 ? (20% of that ordid)
why 1,700 of 3,700 ? (46% of that ordid)
0
 
jrm213jrm213Commented:
@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

select ORID, count(ORID) as feed_count from feed 
group by ORID

Open in new window


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

Open in new window


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

Open in new window


I think this query would also take a very long time to run.
0
 
myyisAuthor Commented:
worked! great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.