Got it, let's fix this. You need to sort your result set based on the VALUE associated with NAME = 'name1', but also ensure all records with the same ID stay grouped together. Here's the adjusted query that does exactly that:

Modified Query #

SELECT * 
FROM (
    SELECT 
        crm_leads.id, 
        f.name, 
        fv.value,
        -- Calculate the dense_rank using the name1 value for each ID group
        dense_rank() OVER (
            ORDER BY (
                -- Get the VALUE where NAME is 'name1' for the current ID
                MAX(CASE WHEN f.name = 'name1' THEN fv.value END) 
                OVER (PARTITION BY crm_leads.id)
            )
        ) AS offset_ 
    FROM crm_leads 
    INNER JOIN crm_modules AS m ON crm_leads.module_id = m.id 
    INNER JOIN crm_fields AS f ON f.module_id = m.id 
    LEFT JOIN crm_field_values AS fv ON fv.lead_id = crm_leads.id AND fv.field_id = f.id 
    LEFT JOIN crm_field_type_values AS ftv ON ftv.field_id = f.id 
    WHERE crm_leads.domain_uuid = '6191af69-9cb5-44f7-b455-3eae6f81d01d' 
      AND m.id = 41 
) AS result_offset
-- Final sort to group IDs and order by the name1 value
ORDER BY offset_, id, name;

How This Works #

1. Grab the name1 value per ID: The subquery MAX(CASE WHEN f.name = 'name1' THEN fv.value END) OVER (PARTITION BY crm_leads.id) fetches the VALUE for name1 for every record in the same ID group. Since each ID only has one name1 entry, MAX just ensures we get that single value without duplicates.
2. Rank with dense_rank: We use this per-ID name1 value as the sorting key for dense_rank(), so all records in the same ID group get the same rank. This keeps them grouped when we sort later.
3. Final sorting: The outer ORDER BY offset_, id, name first sorts by the rank (which corresponds to the name1 value order), then keeps all same-ID records together, and optionally sorts by name within the ID group for consistency.

Expected Output #

Running this query will give you exactly the sorted result you're looking for:

内容的提问来源于stack exchange，提问作者Jevgen Hiltaichuk