Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

get the max row count ==> having count(*) = max count(*)

redneo01Jul 11 2017 — edited Jul 12 2017

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:

  

SERIESTATIONKMchecktypeHRcount
5266station A
015:26:01D         15:27:3921
5266station A
015:26:01D         15:33:253
5266station A
199415:28:01P         15:36:043
5266station A
199415:28:01P         15:31:4121
5266station B
535315:30:00P         15:38:073
5266station B
535315:30:00P         15:33:4521
7100station A
917715:32:00P         15:35:2827
7100station A
917715:32:00P         15:39:5019
7100station A
1222915:33:30P         15:36:4227
7100station A
1222915:33:30P         15:41:0519
7100station A
1537915:35:00P         15:37:5427
7100station B
1537915:35:00P         15:43:0219

the result that I want:

     

SERIESTATIONKMchecktypeHRcount
5266station A
015:26:01D         15:27:3921
5266station A
199415:28:01P         15:31:4121
5266station B
535315:30:00P         15:33:4521
7100station A
917715:32:00P         15:35:2827
7100station A
1222915:33:30P         15:36:4227
7100station A
1537915:35:00P         15:37:5427

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
This post has been answered by Etbin on Jul 11 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 9 2017
Added on Jul 11 2017
9 comments
3,589 views