0

Would you help me out as I am new to sql. I am using the following mySQL query:

SELECT * FROM tags WHERE TagNo LIKE '%' ORDER BY TagNo DESC, id DESC;

My id is auto-incremented every time a new TagNo is added.

I get this:

TagNo, id: 

 - 9, 4 
 - 9, 3  
 - 9, 2  
 - 9, 1  
 - 8, 400  
 - 8, 399  
 - 8, 398  
 - 8, 397

But I need the latest 3 id for any given TagNo.

TagNo, id: 

- 9, 4 
- 9, 3  
- 9, 2  

- 8, 400  
- 8, 399  
- 8, 398

I tried this from Bill Karwin post but GROUP By only shows 1 tag and having COUNT(*) seems to restrict only id with value < 3 .. id dynamically grows per TagNo

SELECT i1.* FROM database i1 LEFT OUTER JOIN ct_database i2 ON (i1.TagNo = i2.TagNo AND i1.id < i2.id) GROUP BY i1.id HAVING COUNT(*) < 3 ORDER BY TagNo

I've been trying for a week to figure out.It would be great if someone can help.

lee
  • 31
  • 4
  • I would suggest you using LIMIT function. And check out the link it might be helpful.MYSQL - ORDER BY & LIMIT http://stackoverflow.com/questions/4708708/mysql-order-by-limit – katmanco Nov 01 '16 at 15:06
  • It seems like you would possibly need more than 1 tag number at a time. If this is correct, Limit would not necessarily work; and it seems the ID's could grow at disproportionate rates. So for each tag you need to get the ID equal to or greater than the 3rd newest ID for each tag. is that correct? – xQbert Nov 01 '16 at 15:34
  • @xQbert Yes you are correct. I did SELECT * FROM tags WHERE TagNo LIKE '%' ORDER BY TagNo DESC, id DESC LIMIT 3; It only gives me the 1st 3 from a single Tag and all other Tags are ignored. How can I solve this? – lee Nov 01 '16 at 22:26
  • @BillKarwin Updated my question. Hope to seek you expert advise – lee Nov 02 '16 at 01:13

0 Answers0