# SQL Generation Instructions - Column Order Precision

## CRITICAL: Output Format
- Generate ONLY the SQL query
- No markdown, no comments, no explanation
- End with semicolon

## Step 1: Evidence Takes Priority

When evidence is provided, it OVERRIDES everything:
- **Column mappings**: "X refers to Y" → Use Y for X
- **Multi-column mappings**: "name refers to FirstName, LastName" → Return BOTH in that order
- **Formulas**: Copy exactly as shown
- **Operators**: Use exact operators (> not >=, < not <=)
- **Values**: Use exact values and format

Key Evidence Patterns:
- "refers to X, Y" → Return multiple columns in listed order
- "X > value" → Use > NOT >=
- "percentage = formula" → Use exact formula
- "SeriesCode = X" → Match exact case and format

## Step 2: Column Selection and Ordering

### CRITICAL RULE: Column Order Matters

The order of columns in SELECT must match the order they appear in the question:
1. **Question Order**: "What is the name and age" → `SELECT name, age`
2. **Evidence Order**: "refers to LastName, FirstName" → `SELECT LastName, FirstName`
3. **Natural Reading**: Follow left-to-right appearance in question

### Column Type Priority

**Always prefer human-readable columns over IDs:**
- "Which person" → FirstName, LastName NOT PersonID
- "What country" → Country.Name NOT Country.Code
- "Which store" → StoreName NOT StoreID
- "Capital city" → City.Name NOT Country.Capital (ID)

**Exception**: Only use IDs when explicitly requested:
- "What is the ID" → Return ID
- "Provide the order ID" → Return OrderID

### Multi-Column Requests

When question asks for multiple things:
- "name, capital city and official language" → Return ALL three in that order
- "List the X and Y" → Return both X and Y
- "Provide A, B and C" → Return all three

## Step 3: Query Patterns

### Pattern A: Entity Selection
**Question**: "What/Which [entity]..."
**Column Rule**: Return entity identifier(s) in question order
```sql
SELECT entity_name FROM table WHERE conditions
```

### Pattern B: Counting
**Question**: "How many..."
**Column Rule**: Return ONLY COUNT
```sql
SELECT COUNT(*) FROM table WHERE conditions
```
- Use COUNT(DISTINCT col) only when "different/unique" mentioned
- Use COUNT(CASE WHEN) for conditional counting

### Pattern C: Top/Most/Highest Entity
**Question**: "[Entity] with most/highest..."
**Template**:
```sql
SELECT entity_columns
FROM table
ORDER BY metric DESC
LIMIT 1
```
**Column Rule**: Return entity, NOT the metric (unless both requested)

### Pattern D: Multiple Information Request
**Question**: "What is the X, Y and Z of..."
**Column Rule**: Return X, Y, Z in that exact order
```sql
SELECT X, Y, Z FROM table WHERE conditions
```

### Pattern E: Yes/No Questions
**Question**: "Is/Does..."
- Evidence format determines response:
  - Shows 'yes'/'no': `SELECT CASE WHEN condition THEN 'yes' ELSE 'no' END`
  - Otherwise: `SELECT COUNT(*) > 0`

## Step 4: Join Selection Rules

### Choose Correct Tables Based on Context

1. **Evidence specifies tables**: Use those exact tables
2. **Multiple paths exist**: Choose most direct path
3. **Ambiguous columns**: Qualify with table name

### Foreign Key Priority
- Use primary/foreign key joins when available
- Avoid joining through intermediate tables unnecessarily
- Check relationship_mapper output for correct paths

## Step 5: Common Pitfall Fixes

### Column Order Errors
- ❌ `SELECT id, name` when asked "name and id"
- ✅ `SELECT name, id` (follow question order)

### Missing Columns
- ❌ `SELECT city` when asked "city and country"
- ✅ `SELECT city, country` (return ALL requested)

### Wrong Identifier Type
- ❌ `SELECT Capital` when Capital is an ID
- ✅ `SELECT City.Name` joined on Capital = City.ID

### COUNT vs SUM
- COUNT(*): Total rows
- COUNT(column): Non-null values
- SUM(column): Add numeric values
- Never use SUM to count records

### Reserved Words
- Always use backticks for: `transaction`, `order`, `group`
- Check reserved_word_detector output

## Step 6: Verification Checklist

Before finalizing query:
1. ✓ Column order matches question order?
2. ✓ ALL requested columns included?
3. ✓ Human-readable columns used (not IDs)?
4. ✓ Evidence mappings followed exactly?
5. ✓ Correct tables joined based on context?
6. ✓ ORDER BY LIMIT used for "top/most"?
7. ✓ Reserved words properly quoted?

## Priority Rules

When in conflict:
1. Evidence overrides all
2. Question order determines column order
3. Human-readable over IDs
4. Simple query over complex
5. Direct joins over subqueries

Remember: The database analysis provides exact guidance. Follow it precisely, especially column order instructions.