Hi Everybody,
I Try to get the maximum of the column "count" to show only rows who appear the most
Here's my code:
select max(count) from VarCpt
group by serie,station,KM,check,HR,type
order by cpt
here's an example of data:
| SERIE | STATION | KM | check | type | HR | count |
| 5266 | station A
| 0 | 15:26:01 | D | 15:27:39 | 21 |
| 5266 | station A
| 0 | 15:26:01 | D | 15:33:25 | 3 |
| 5266 | station A
| 1994 | 15:28:01 | P | 15:36:04 | 3 |
| 5266 | station A
| 1994 | 15:28:01 | P | 15:31:41 | 21 |
| 5266 | station B
| 5353 | 15:30:00 | P | 15:38:07 | 3 |
| 5266 | station B
| 5353 | 15:30:00 | P | 15:33:45 | 21 |
| 7100 | station A
| 9177 | 15:32:00 | P | 15:35:28 | 27 |
| 7100 | station A
| 9177 | 15:32:00 | P | 15:39:50 | 19 |
| 7100 | station A
| 12229 | 15:33:30 | P | 15:36:42 | 27 |
| 7100 | station A
| 12229 | 15:33:30 | P | 15:41:05 | 19 |
| 7100 | station A
| 15379 | 15:35:00 | P | 15:37:54 | 27 |
| 7100 | station B
| 15379 | 15:35:00 | P | 15:43:02 | 19 |
the result that I want:
| SERIE | STATION | KM | check | type | HR | count |
| 5266 | station A
| 0 | 15:26:01 | D | 15:27:39 | 21 |
| 5266 | station A
| 1994 | 15:28:01 | P | 15:31:41 | 21 |
| 5266 | station B
| 5353 | 15:30:00 | P | 15:33:45 | 21 |
| 7100 | station A
| 9177 | 15:32:00 | P | 15:35:28 | 27 |
| 7100 | station A
| 12229 | 15:33:30 | P | 15:36:42 | 27 |
| 7100 | station A
| 15379 | 15:35:00 | P | 15:37:54 | 27 |
Is it possible to get only the rows where series, station, KM, check, type HR and max (count(*)) of the two rows.
And a last question, is it possible to add an "id" in case there's the same value in the colum count for the same serie,station, km, check...
Thank you in advance