0
List<Small_Capital_Field_PM__c> lstSmallCapital = [Select Name, Account__c,
                                                   Account__r.Name, CFN__c,
                                                   Lot_Serial_Number__c, SN_Left_Behind__c 
                                                   from Small_Capital_Field_PM__c 
                                                   where Account__c=:Acntid and Status__c=:strOpen 
                                                   ORDER BY CFN__c, Lot_Serial_Number__c];

I want the display to be sorted by CFN__c and Lot_Serial_Number__c should in be ascending order.

Currently the VF page displays the result based on Sorted CFN only. It is not considering the serial number ascending order

KINDLY HELP ME

Example-- It should display this way--- Kindly treat the below table as a vertical table

CFN A001 A0012 A001
SERIAL 123 234 345

currently is displaying as below

CFN A001 A001 A0012
SERIAL 123 334 245

3
  • Your order by clause seems to me to be doing exactly what you are asking of it. In your Order By, because CFN__c is first, all the A001 values would come to the top. Within all of those, the Serial values would then go in ascending order. If you want serial first, then put that first.
    – pchittum
    Sep 2 '15 at 15:47
  • Hi Peter. Thank you for the comments. If I put Serial first, it is not grouping the CFN__c field. It goes like A001 E001 A001 .....
    – PSH
    Sep 2 '15 at 16:41
  • If neither of those sorts work then you may have to do something such as add a custom formula field where you build a string that combines the two values in such a way that you get the sort you want when you sort by that custom field.
    – Keith C
    Sep 2 '15 at 16:56
0

Let's say you have the following:

Animal   |   Height
--------------------
Horse    |   11
Horse    |    9
Dog      |    3
Horsefly |    9
Cow      |    9
Horsefly |   10
Cow      |   10

If I told a query of this data to do the following:

ORDER BY Animal, Height

The natural sorting behavior for SOQL (and SQL) would produce the following

Animal   |   Height
--------------------
Cow      |    9
Cow      |   10

Dog      |    3

Horse    |    8
Horse    |   11

Horsefly |    9
Horsefly |   10

What you seem to be asking the query to do is more like this:

Animal   |   Height
--------------------
Cow      |    9
Cow      |   10

Dog      |    3

Horse    |    8
Horsefly |    9
Horsefly |   10
Horse    |   11

The values A001 and A0012 are String/Text data. As such, all of the A001 CFN values will appear at the top. Only within those will the Serial be sorted by ascending order.

Without a larger data set, it is hard to suggest a work around. But there exists the possibility to create a hidden formula field that you could use. For instance, if all CFN values that began with A001 were meant to be a grouping of data, your formula field could simply grab the first 4 characters of the CFN field.

You can even contact support to make this formula field indexed provided it is deterministic (which it would be in this case). This would give you better performance for this query as your data grows.

Hopefully this makes things more clear, but bottom line, your ORDER BY clause is doing exactly what you're asking it to do.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.