Chained CRUD - Sort, Group, Join & Selection

Sort, group, join, and selection methods all return the cDataBase object, allowing free stacking and combination. For architecture details, see Chained CRUD Overview.

---

Sorting Methods

OrderBy - ASC Sorting

Can be called multiple times to stack multiple sort fields.

Syntax

Function OrderBy(ByVal FieldName As String) As cDataBase

Example

' Single field sorting
db.Table("users").OrderBy("age").RowRead

' Multi-field sorting (by age, then by name)
db.Table("users").OrderBy("age").OrderBy("name").RowRead

OrderByDesc - DESC Sorting

Syntax

Function OrderByDesc(ByVal FieldName As String) As cDataBase

Example

' Sort by ID descending
db.Table("users").OrderByDesc("id").RowRead

' Mixed sorting (age descending, name ascending)
db.Table("users").OrderByDesc("age").OrderBy("name").RowRead

OrderByRaw - Custom Sort Clause

Syntax

Function OrderByRaw(ByVal RawClause As String) As cDataBase

Example

' Compound sorting
db.Table("users").OrderByRaw("age DESC, name ASC").RowRead

' Function sorting
db.Table("users").OrderByRaw("RAND()").RowRead

---

Grouping Methods

GroupBy - Group Query

Can be called multiple times to stack multiple group fields.

Syntax

Function GroupBy(ByVal FieldName As String) As cDataBase

Example

' Group by department
db.Table("users").Columns("dept,COUNT(*) AS cnt").GroupBy("dept").RowRead

' Multi-field grouping
db.Table("orders").Columns("year,month,SUM(amount) AS total").GroupBy("year").GroupBy("month").RowRead

Having - Group Filter

Used with GroupBy to filter grouped results.

Syntax

Function Having(ByVal Condition As String) As cDataBase

Example

' Departments with count greater than 5
db.Table("users").Columns("dept,COUNT(*) AS cnt").GroupBy("dept").Having("cnt>5").RowRead

' Departments with average salary greater than 8000
db.Table("users").Columns("dept,AVG(salary) AS avg_sal").GroupBy("dept").Having("avg_sal>8000").RowRead

---

Join Methods

Join - INNER JOIN

Syntax

Function Join(ByVal TableName As String, ByVal OnCondition As String) As cDataBase

Example

' Inner join
db.Table("orders") _
    .Columns("orders.id,users.name,orders.amount") _
    .Join("users", "orders.user_id=users.id") _
    .RowRead

LeftJoin - LEFT JOIN

Syntax

Function LeftJoin(ByVal TableName As String, ByVal OnCondition As String) As cDataBase

Example

' Left join (keep all orders, even without matching users)
db.Table("orders") _
    .Columns("orders.id,users.name") _
    .LeftJoin("users", "orders.user_id=users.id") _
    .OrderByDesc("orders.id") _
    .RowRead

RightJoin - RIGHT JOIN

Syntax

Function RightJoin(ByVal TableName As String, ByVal OnCondition As String) As cDataBase

Example

' Right join
db.Table("orders") _
    .RightJoin("users", "orders.user_id=users.id") _
    .RowRead

Multi-Table Join Stacking

' Three-table join
db.Table("orders") _
    .Columns("orders.id,users.name,products.title") _
    .Join("users", "orders.user_id=users.id") _
    .Join("products", "orders.product_id=products.id") _
    .Where("orders.amount>?", 100) _
    .OrderByDesc("orders.id") _
    .RowRead

---

Column Selection Methods

Columns - Specify Query Columns

Defaults to SELECT *, changes to specified columns after calling. Supports comma-separated string or multiple parameter forms.

Note: Method name uses Columns instead of Select to avoid conflict with VB6 reserved keyword Select Case.

Syntax

Function Columns(ParamArray Cols() As Variant) As cDataBase

Example

' String form
db.Table("users").Columns("id,name,age").RowRead

' Multiple parameter form
db.Table("users").Columns("id", "name", "age").RowRead

' With aggregate functions
db.Table("users").Columns("dept,COUNT(*) AS cnt").GroupBy("dept").RowRead

' With JOIN
db.Table("orders").Columns("orders.id,users.name").Join("users", "orders.uid=users.id").RowRead

Distinct - Distinct Query

Syntax

Function Distinct() As cDataBase

Example

' Query distinct departments
db.Table("users").Distinct.Columns("dept").RowRead

' Query distinct cities
db.Table("users").Distinct.Columns("city").Where("age>?", 18).RowRead

---

Last Updated: 2026-06-26