For SQL statements, a maximum of three records can be obtained for the same parent ID.

Source: Internet
Author: User
SQL about the same parent ID, a maximum of three records table imginfo can be obtained.
Id fid miaoshu
1 3 aaaaaaa
2 3 sssssss
3 3 fffff
4 4 uuuuuuuuu
5 5 gfgfgfgfgf
6 5 ddfdfdfd
7 9 fdffdfd
8 9 dfdfdfd
9 9 dfdfdf
10 9 popopop
11 3 tgtrtyrtyry
12 5 fdsjfdfdl

SELECT * FROM 'imginfo' WHERE fid in (3,5, 9) order by 'id' DESC
All content of the above statements fid, 5, and 9 is obtained.

Now I only want to get up to three identical FIDS, and the sorting remains unchanged. how can I write SQL statements?
Please kindly advise. Thank you!


Reply to discussion (solution)

If you obtain the SQL statement and get three values for each value in the php end, it will not end?

If you get only the first three items after the order, you can use limit0, 3;


select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by a.fid,a.id

The SQL statement is as follows:
Select. * from imginfo a where 3> (select count (*) from imginfo where fid =. fid and id>. id and fid in (3,5, 9) and fid in (3,5, 9) order by. fid,. id

If you only sort by ID in descending order, sort and change it.
Select. * from imginfo a where 3> (select count (*) from imginfo where fid =. fid and id>. id and fid in (3,5, 9) and fid in (3,5, 9) order by. id desc

You can.

Learning! Thank you, moderator.


select a.* from imginfo a where 3 > (select count(*) from imginfo where fid = a.fid and id > a.id and fid in(3,5,9)) and fid in(3,5,9) order by a.fid,a.id

The moderator would like to ask how to sort by the number of entries? For example, if the fid is 3 and there are 5 results 9 and 10 results 5 and 3 orders 9 and 3 5, how can this problem be achieved? thank you!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.