lundi 7 septembre 2015

SQL: Sort by number of same values in one column without aggregating/contracting

I have

objectType | object

107          Boeing
107          Airbus
323          Audi
323          Mercedes
323          BMW
985          Ducati

I want

objectType | object   | groupSizeOfObjectType (for clarification, optional)

323          Audi       3
323          Mercedes   3
323          BMW        3
107          Boeing     2
107          Airbus     2
985          Ducati     1

I know that group by and count(*) will get me:

objectType | object   | groupSizeOfObjectType (for clarification, optional)

323          Audi       3
107          Boeing     2
985          Ducati     1

But this is not what I need, as I need all objects. Any hints on how I should do this?

My actual problem is a little more complicated (it's the same problem twice), would also be great if someone would like to directly answer it:

I have

objectType | objectKind |  object

107          B             Boeing
107          B             Airbus
323          D             Audi
323          D             Mercedes
323          D             BMW
323          D             Lexus
985          B             Ducati

I need it to be ordered so that most common objectKinds are first, then afterwards so that most common objectTypes are first. So I can get:

objectType | objectKind |  object


323          D             Audi      
323          D             Mercedes
323          D             BMW
323          D             Lexus
107          B             Boeing
107          B             Airbus
985          B             Ducati

Aucun commentaire:

Enregistrer un commentaire