COUNT and GROUP BY SQL -

this question has answer here:

• how group , count using mysql 4 answers

i have example data in left table , expected output on right side.

the requirement of output are:

• the count counts serial , group time , model.
• if there more 1 serial have same value, choose last one.

for example, @ time 6, there 3 model serial number 1,2,7, count serial 7 because first row (1-a-6) has duplicate serial next row (1-a-7) don't count (1-a-6).

the question is, how can make sql query data output in 1 query?

the technique below uses row_number , subqueries last instance of each serial, group by count perform aggregate. these should supported common dbms's (since didn't specify sql server or mysql or whatever).

select     [time],     [model],     count(*) [count] (     select          [time],         [model],         [serial],         -- paritioning serial, last instance of serial         row_number() on (partition serial order rownum desc) rownum     (         select             [time],             [model],             [serial],             -- add row number find last value per requirements             row_number() on (order [serial]) rownum         @t -- table here         ) t     ) t rownum = 1 -- last instance of serial group     [time],     [model] order     [time] 

testing sql server:

declare @t table ([serial] int, [model] char(1), [time] int) insert @t values (1,'a', 6),(1, 'a', 7),(2,'a',6),(2,'a',7),     (2,'a',9),(3,'a',8),(4,'a',8),(5,'b',9),(6,'b',9),(7,'a',6),     (8,'b',10),(9,'b',8),(10,'b',8) 

the desired output achieved:

time        model count ----------- ----- ----------- 6               1 7               1 8               2 8           b     2 9               1 9           b     2 10          b     1