Help with another mysql query

Answer 1

You could redesign your table to also have a counter:

ID    product_id  consecutive
 8      bat         1
 9      bat         2
10      abc         1
11      cat         1
12      dog         1
13      dog         2

When inserting to the table, first check the last entry and its consecutive counter. If you are inserting the same element, increase the counter, otherwise insert 1. Something like:

INSERT INTO Table (product_id, consecutive) VALUES
('newProd', CASE (SELECT product_id FROM Table WHERE ID = MAX(ID))
              WHEN 'newProd' THEN
                           (SELECT consecutive FROM Table WHERE ID = MAX(ID)) + 1
              ELSE 1
            END);

Then you can make your selection as:

SELECT product_id, consecutive
FROM Table
WHERE ID IN
  (SELECT MAX(ID)
   FROM Table
   GROUP BY product_id)

---

Original answer was:

SELECT Filename, COUNT(Filename) as Count
FROM Table
GROUP BY Filename

Answer 2

If you only want 'newly added' records, you will have to indicate somehow what a newly added record is. Your dbms may support dating by default on all records (I have an inkling that Ingres may do this, but I may be imagining it - it's been a while since I've used it), but you will probably need to add something to the row yourself.

You could add a datetime field to filter on, then you can do as Zed has done:

SELECT Filename, COUNT(Filename) as Count
FROM Table
WHERE DateAdded > (FilterDate)
GROUP BY Filename

(EDIT where FilterDate is the DateTime after which you deem a record to be new, for example you could set this to Now minus 60 minutes - the syntax will change depending on DBMS)

or you could add a 'new' flag bit column and set it to true for each new record, them turn it off after it is read or some arbitary interval.

Answer 3

SELECT COUNT(xbrygame_scrnsmkv.id) FROM xbrygame_scrnsmkv 
WHERE xbrygame_scrnsmkv.id >(SELECT  MAX(xbrygame_scrnsmkv.id) 
                             FROM xbrygame_scrnsmkv 
                             WHERE xbrygame_scrnsmkv.product_id= (SELECT  DISTINCT xbrygame_scrnsmkv.product_id 
                                                             FROM xbrygame_scrnsmkv 
                                                             ORDER BY xbrygame_scrnsmkv.id DESC LIMIT 5,1)) 
   GROUP BY xbrygame_scrnsmkv.product_id