> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Top K

> ParadeDB is optimized for quickly finding the Top K results in a table

ParadeDB is highly optimized for quickly returning the Top K results out of the index. In SQL, this means queries that contain an `ORDER BY...LIMIT`:

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description ||| 'running shoes'
  ORDER BY rating
  LIMIT 5;
  ```

  ```ts Drizzle theme={null}
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(search.matchAny(mockItems.description, "running shoes"))
    .orderBy(mockItems.rating)
    .limit(5);
  ```

  ```python Django theme={null}
  from paradedb import MatchAny, ParadeDB

  MockItem.objects.filter(
      description=ParadeDB(MatchAny('running shoes'))
  ).order_by('rating').values('description', 'rating', 'category')[:5]
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.match_any(MockItem.description, "running shoes"))
      .order_by(MockItem.rating)
      .limit(5)
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  MockItem.search(:description)
          .matching_any("running shoes")
          .order(:rating)
          .select(:description, :rating, :category)
          .limit(5)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
      .OrderBy(item => item.Rating)
      .Select(item => new { item.Description, item.Rating, item.Category })
      .Take(5)
      .ToListAsync();
  ```
</CodeGroup>

In order for a Top K query to be executed by ParadeDB vs. vanilla Postgres, all of the following conditions must be met:

1. All `ORDER BY` fields must be indexed. If they are text fields, they [must use the literal tokenizer](#sorting-by-text).
2. At least one ParadeDB text search operator must be present at the same level as the `ORDER BY...LIMIT`.
3. The query must have a `LIMIT`.
4. With the exception of `lower`, ordering by expressions is not supported -- only the raw fields themselves.

To verify that ParadeDB is executing the Top K, look for a `Custom Scan` with a `TopKScanExecState` in the `EXPLAIN` output:

```sql theme={null}
EXPLAIN SELECT description, rating, category
FROM mock_items
WHERE description ||| 'running shoes'
ORDER BY rating
LIMIT 5;
```

<Accordion title="Expected Response">
  ```csv theme={null}
                                                                                                     QUERY PLAN
  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Limit  (cost=10.00..10.02 rows=3 width=552)
     ->  Custom Scan (ParadeDB Base Scan) on mock_items  (cost=10.00..10.02 rows=3 width=552)
           Table: mock_items
           Index: search_idx
           Segment Count: 1
           Exec Method: TopKScanExecState
           Scores: false
              TopK Order By: rating asc
              TopK Limit: 5
           Tantivy Query: {"with_index":{"query":{"match":{"field":"description","value":"running shoes","tokenizer":null,"distance":null,"transposition_cost_one":null,"prefix":null,"conjunction_mode":false}}}}
  (10 rows)
  ```
</Accordion>

If any of the above conditions are not met, the query cannot be fully optimized and you will not see a `TopKScanExecState` in the `EXPLAIN` output.

## Tiebreaker Sorting

To guarantee stable sorting in the event of a tie, additional columns can be provided to `ORDER BY`.

This is particularly important when sorting by `pdb.score(...)`, as equal scores can result in non-deterministic orderings. As noted above, all tiebreaker columns must be indexed to receive the Top K optimization.

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description ||| 'running shoes'
  ORDER BY rating, id
  LIMIT 5;
  ```

  ```ts Drizzle theme={null}
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(search.matchAny(mockItems.description, "running shoes"))
    .orderBy(mockItems.rating, mockItems.id)
    .limit(5);
  ```

  ```python Django theme={null}
  from paradedb import MatchAny, ParadeDB

  MockItem.objects.filter(
      description=ParadeDB(MatchAny('running shoes'))
  ).order_by('rating', 'id').values('description', 'rating', 'category')[:5]
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.match_any(MockItem.description, "running shoes"))
      .order_by(MockItem.rating, MockItem.id)
      .limit(5)
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  MockItem.search(:description)
          .matching_any("running shoes")
          .order(:rating, :id)
          .select(:description, :rating, :category)
          .limit(5)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item => EF.Functions.MatchAny(item.Description, "running shoes"))
      .OrderBy(item => item.Rating)
      .ThenBy(item => item.Id)
      .Select(item => new { item.Description, item.Rating, item.Category })
      .Take(5)
      .ToListAsync();
  ```
</CodeGroup>

<Note>
  ParadeDB is currently able to handle 3 `ORDER BY` columns. If there are more
  than 3 columns, the `ORDER BY` will not be efficiently executed by ParadeDB.
</Note>

## Sorting by Text

If a text field is present in the `ORDER BY` clause, it must be indexed with the [literal](/documentation/tokenizers/available-tokenizers/literal) or
[literal normalized](/documentation/tokenizers/available-tokenizers/literal-normalized) tokenizer.

Sorting by lowercase text using `lower(<text_field>)` is also supported. To enable this, the expression `lower(<text_field>)` must be indexed
with either the literal or literal normalized tokenizer. See [indexing expressions](/documentation/indexing/indexing-expressions) for more information.

```sql theme={null}
CREATE INDEX search_idx ON mock_items
USING bm25 (id, (lower(description)::pdb.literal))
WITH (key_field='id');
```

This allows sorting by lowercase to be optimized.

<CodeGroup>
  ```sql SQL theme={null}
  SELECT description, rating, category
  FROM mock_items
  WHERE description ||| 'sleek running shoes'
  ORDER BY lower(description)
  LIMIT 5;
  ```

  ```ts Drizzle theme={null}
  import { sql } from "drizzle-orm";
  import { search } from "@paradedb/drizzle-paradedb";

  await db
    .select({
      description: mockItems.description,
      rating: mockItems.rating,
      category: mockItems.category,
    })
    .from(mockItems)
    .where(search.matchAny(mockItems.description, "sleek running shoes"))
    .orderBy(sql`lower(${mockItems.description})`)
    .limit(5);
  ```

  ```python Django theme={null}
  from django.db.models.functions import Lower
  from paradedb import MatchAny, ParadeDB

  MockItem.objects.filter(
      description=ParadeDB(MatchAny('sleek running shoes'))
  ).order_by(Lower('description')).values('description', 'rating', 'category')[:5]
  ```

  ```python SQLAlchemy theme={null}
  from sqlalchemy import func, select
  from sqlalchemy.orm import Session
  from paradedb.sqlalchemy import search

  stmt = (
      select(MockItem.description, MockItem.rating, MockItem.category)
      .where(search.match_any(MockItem.description, "sleek running shoes"))
      .order_by(func.lower(MockItem.description))
      .limit(5)
  )

  with Session(engine) as session:
      session.execute(stmt).all()
  ```

  ```ruby Rails theme={null}
  description = MockItem.arel_table[:description]
  lower_description = Arel::Nodes::NamedFunction.new("LOWER", [description])

  MockItem.search(:description)
          .matching_any("sleek running shoes")
          .order(Arel::Nodes::Ascending.new(lower_description))
          .select(:description, :rating, :category)
          .limit(5)
  ```

  ```cs EF Core theme={null}
  await dbContext
      .MockItems.Where(item =>
          EF.Functions.MatchAny(item.Description, "sleek running shoes")
      )
      .OrderBy(item => item.Description.ToLower())
      .Select(item => new { item.Description, item.Rating, item.Category })
      .Take(5)
      .ToListAsync();
  ```
</CodeGroup>

## Sorting by JSON

Ordering by JSON subfield is on the roadmap but not yet supported. For example, this query will not receive an optimized
Top K scan:

```sql theme={null}
SELECT id, description, metadata
FROM mock_items
WHERE description ||| 'sleek running shoes'
ORDER BY metadata->'weight'
LIMIT 5;
```