llm_last

The llm_last function is used to extract the least relevant result from a set of rows based on a model’s prompt and input columns. It operates over a set of rows, generally combined with a GROUP BY clause, to return the least relevant row for each group.

Modalities

Text

Select the least relevant row using tabular text columns.

SELECT llm_last(
  {'model_name': 'gpt-4o'},
  {
    'prompt': 'professional work productivity',
    'context_columns': [
      {'data': product_name},
      {'data': product_description}
    ]
  }
)
FROM products;

Image

Select the least matching visual example with type: 'image'.

SELECT
  category,
  llm_last(
    {'model_name': 'gpt-4o'},
    {
      'prompt': 'Which product image looks least professional?',
      'context_columns': [
        {'data': product_name},
        {'data': image_url, 'type': 'image'}
      ]
    }
  ) AS weakest_image
FROM product_catalog
GROUP BY category;

Voice

Select using spoken content via type: 'audio' and transcription_model (OpenAI / Azure).

SELECT
  agent_id,
  llm_last(
    {'model_name': 'gpt-4o'},
    {
      'prompt': 'Pick the call least relevant to resolving billing issues',
      'context_columns': [{
        'data': file_path,
        'type': 'audio',
        'transcription_model': 'whisper-1'
      }]
    }
  ) AS least_relevant_call
FROM support_calls
GROUP BY agent_id;

1. Usage Examples

1.1. Example without `GROUP BY`

Retrieve the least relevant product feature across all rows:

SELECT llm_last(
    {'model_name': 'gpt-4o'},
    {'prompt': 'professional work productivity', 'context_columns': [{'data': product_name}, {'data': product_description}]}
) AS last_product_feature
FROM VALUES
    ('MacBook Pro', 'High-performance laptop with M2 chip and Retina display'),
    ('AirPods Pro', 'Wireless earbuds with active noise cancellation'),
    ('iPad Air', 'Lightweight tablet perfect for creativity and productivity')
AS t(product_name, product_description);

Description: This query returns the product least relevant to the given sentence from all product descriptions and product names.

1.2. Example with `GROUP BY`

Retrieve the least relevant product feature for each product category:

SELECT category,
       llm_last(
           {'model_name': 'gpt-4o'},
           {'prompt': 'high-quality audio equipment for entertainment', 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM VALUES
    ('Electronics', 'Premium Headphones', 'High-quality wireless headphones with superior sound', 89.99),
    ('Electronics', 'Gaming Mouse', 'Precision gaming mouse with RGB lighting', 45.99),
    ('Electronics', 'Wireless Keyboard', 'Ergonomic wireless keyboard with backlight', 79.99),
    ('Books', 'Python Programming', 'Complete guide to Python programming language', 39.99),
    ('Books', 'Data Science Guide', 'Comprehensive data science methodology book', 49.99),
    ('Books', 'Machine Learning', 'Introduction to machine learning algorithms', 59.99)
AS products(category, product_name, product_description, price)
GROUP BY category;

Description: The query groups the products by category and returns the product least relevant to the given sentence for each group.

1.3. Using a Named Prompt with `GROUP BY`

Use a reusable prompt, such as “least-relevant-detail”, to extract the least relevant feature for each product category:

SELECT category,
       llm_last(
           {'model_name': 'gpt-4o', 'secret_name': 'product_key'},
           {'prompt_name': 'least-relevant-detail', 'version': 1, 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM (
    SELECT * FROM (
        VALUES
            ('Electronics', 'MacBook Pro', 'High-performance laptop with M2 chip'),
            ('Electronics', 'iPhone 15', 'Latest smartphone with advanced camera system'),
            ('Accessories', 'Magic Mouse', 'Wireless mouse with multi-touch surface'),
            ('Accessories', 'USB-C Cable', 'Fast charging cable for modern devices')
    ) AS t(category, product_name, product_description)
)
GROUP BY category;

Description: This example leverages a named prompt (least-relevant-detail) to extract the least relevant feature for each product category. The query groups the results by category.

1.4. Advanced Example with Multiple Columns and `GROUP BY`

Retrieve the least relevant feature for products grouped by category, using both the product name and description:

WITH sample_products AS (
    SELECT * FROM (
        VALUES
            ('Electronics', 'MacBook Pro', 'High-performance laptop with M2 chip'),
            ('Electronics', 'iPhone 15', 'Latest smartphone with advanced camera'),
            ('Electronics', 'iPad Air', 'Lightweight tablet for creativity')
    ) AS t(category, product_name, product_description)
)
SELECT category,
       llm_last(
           {'model_name': 'gpt-4o'},
           {'prompt': 'mobile communication and calls', 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM sample_products
GROUP BY category;

Description: This query extracts the product least relevant to the given sentence from both the product_name and product_description columns, grouped by product category.

2. Input Parameters

2.1 Model Configuration

Parameter: model_name and secret_name

2.1.1 Model Selection

Description: Specifies the model used for text generation.
Example:
{ 'model_name': 'gpt-4o' }

2.1.2 Model Selection with Secret

Description: Specifies the model along with the secret name to be used for authentication when accessing the model.

Example:

{ 'model_name': 'gpt-4o', 'secret_name': 'your_secret_name' }

2.2. Prompt Configuration

Two types of prompts can be used:

Inline Prompt

Directly provides the prompt in the query with context columns.

Example:

{'prompt': 'professional audio equipment', 'context_columns': [{'data': product_name}, {'data': product_description}]}

Named Prompt
- Refers to a pre-configured prompt by name.
- Example:
  {'prompt_name': 'least-relevant-detail'}
Named Prompt with Version
- Refers to a specific version of a pre-configured prompt.
- Example:
  {'prompt_name': 'least-relevant-detail', 'version': 1}

2.3. Context Columns Configuration

Key: context_columns array.
Purpose: Maps table columns to provide input data for the model. Each column can have three properties:
- data: The SQL column data (required)
- name: Custom name for the column to be referenced in the prompt (optional)
- type: Data type — "tabular" (default), "image", or "audio" (audio requires transcription_model)

Example:

'context_columns': [
  {'data': product_name, 'name': 'product'},
  {'data': product_description},
  {'data': image_url, 'type': 'image'},
  {'data': audio_path, 'type': 'audio', 'transcription_model': 'whisper-1'}
]

3. Output

Type: JSON object.
Behavior: Returns the selected row’s input columns as JSON (the least relevant row for the prompt). Optionally pass a JSON schema in model_parameters to constrain the ranking model (OpenAI response_format, Ollama format, Anthropic output_format / tool-use); the SQL return value is still the chosen row.

SELECT llm_last(
  {
    'model_name': 'gpt-4o',
    'model_parameters': '{
      "response_format": {
        "type": "json_schema",
        "json_schema": {
          "name": "relevance",
          "schema": {
            "type": "object",
            "properties": {
              "relevant": { "type": "boolean" },
              "reason": { "type": "string" }
            },
            "required": ["relevant", "reason"],
            "additionalProperties": false
          }
        },
        "strict": true
      }
    }'
  },
  {
    'prompt': 'Pick the least relevant product for a runner',
    'context_columns': [
      {'data': product_name},
      {'data': product_description}
    ]
  }
)
FROM products
GROUP BY category;

Example return value for the selected row:

{
  "product_name": "Wireless Headphones",
  "product_description": "High-quality wireless headphones with noise cancellation."
}

Home

Getting Started

Resource Management

Scalar Functions

Aggregate Functions

Hybrid Search

Operations

Contributing

Modalities

Text

Image

Voice

1. Usage Examples

1.1. Example without `GROUP BY`

1.2. Example with `GROUP BY`

1.3. Using a Named Prompt with `GROUP BY`

1.4. Advanced Example with Multiple Columns and `GROUP BY`

2. Input Parameters

2.1 Model Configuration

2.1.1 Model Selection

2.1.2 Model Selection with Secret

2.2. Prompt Configuration

2.3. Context Columns Configuration

3. Output

​Modalities

​Text

​Image

​Voice

​1. Usage Examples

​1.1. Example without GROUP BY

​1.2. Example with GROUP BY

​1.3. Using a Named Prompt with GROUP BY

​1.4. Advanced Example with Multiple Columns and GROUP BY

​2. Input Parameters

​2.1 Model Configuration

​2.1.1 Model Selection

​2.1.2 Model Selection with Secret

​2.2. Prompt Configuration

​2.3. Context Columns Configuration

​3. Output

Modalities

Text

Image

Voice

1. Usage Examples

1.1. Example without `GROUP BY`

1.2. Example with `GROUP BY`

1.3. Using a Named Prompt with `GROUP BY`

1.4. Advanced Example with Multiple Columns and `GROUP BY`

2. Input Parameters

2.1 Model Configuration

2.1.1 Model Selection

2.1.2 Model Selection with Secret

2.2. Prompt Configuration

2.3. Context Columns Configuration

3. Output