How can I combine results from two SELECT queries with UNION?
Combine current and archived rows in SQLite, choose whether duplicate result rows should disappear, and check the output with paired examples.
Read the guide 4 min read
GEEZ AT WORK
Technology & devices
Questions, practical answers, and useful follow-ups to help you work through the details.
Latest guides
Showing 121–144 of 845 guides
How do I remove duplicate result rows with SELECT DISTINCT?
See how SELECT DISTINCT deduplicates the selected columns, with two SQLite queries and example results showing why adding a column changes the outcome.
Read the guide 4 min read
How can I use CASE to label values in a SELECT result?
Use an ordered SQLite CASE expression to turn assumed stock counts into readable availability labels in query results, without changing stored counts.
Read the guide 4 min read
How can I show each group’s total beside every detail row?
Use a SUM window function partitioned by the group key to keep every detail row and repeat its group’s total beside it.
Read the guide 4 min read
How can I compare each row with the previous row using LAG?
Use LAG() within each device’s ordered series to show its prior reading and calculate the change, including NULL for the first row.
Read the guide 4 min read
What is the difference between RANK and DENSE_RANK in SQLite?
See how tied scores affect rank numbers, and use a partitioned SQLite query to compare both functions within each group.
Read the guide 4 min read
How do I assign a row number within each category?
Use ROW_NUMBER() with PARTITION BY to number rows separately in each category, and add a tie-breaker to make tied prices predictable.
Read the guide 4 min read
How do I use a CTE to make a multi-step SELECT easier to read?
Name an intermediate customer-total calculation in a CTE, then filter those totals in a separate SELECT, with a complete invented example.
Read the guide 4 min read
How can I use a scalar subquery to show a related summary beside each row?
Learn how a correlated scalar subquery can display a category-level price average beside each product in SQLite.
Read the guide 5 min read
How can I use a subquery to filter rows by an aggregate threshold?
Use an aggregate subquery to calculate one comparison value, then filter individual rows against it—with a worked salary example.
Read the guide 4 min read
How can I remove extra spaces before comparing text values?
Use SQLite’s trim() in a comparison to ignore spaces at the ends of a value, and see how trimmed and raw comparisons differ.
Read the guide 4 min read
How can I calculate an average while ignoring missing values?
Use SQLite’s AVG and COUNT on the same column to show each group’s average and how many non-NULL values contributed.
Read the guide 4 min read
How do I extract part of a text value with SQLite substring functions?
Use SQLite’s one-based `substr()` positions to extract a fixed section, keep the rest of a value, or count a starting position from the end.
Read the guide 4 min read
How can I combine text columns into one display value in SQLite?
Combine name columns with SQLite’s || operator, and use an explicit NULL-aware expression to keep available names when a component is missing.
Read the guide 4 min read
How can I sort dates stored as ISO text in SQLite?
Sort valid, consistently zero-padded YYYY-MM-DD text with ORDER BY; see an example, expected output, and why mixed formats need attention.
Read the guide 4 min read
How do I avoid an integer result when dividing aggregate totals?
See why SQLite can truncate a ratio of integer totals, how to make the calculation fractional, and what to check in a worked example.
Read the guide 4 min read
How can I find every row tied for the highest value in a group?
Join each group’s maximum value back to the original rows to return every record tied for the top score.
Read the guide 5 min read
How can I count distinct values within each group?
Use GROUP BY with COUNT(DISTINCT ...) to count unique, non-NULL values per group, and check the result against a small example.
Read the guide 5 min read
How can I count rows that meet a condition inside each group?
Use a grouped total count alongside a conditional count to compare all recorded sessions with attended sessions for each person.
Read the guide 4 min read
How do I compare records within the same table using a self-join?
Use two aliases for one employee table to match each employee’s manager ID with the manager’s employee ID.
Read the guide 4 min read
How do I join two tables using more than one matching column?
Match records on both course and term, with runnable SQLite examples showing how a course-only join can create incorrect extra matches.
Read the guide 5 min read
How can I test that a function raises the expected exception?
Use unittest.assertRaises around just the invalid call, so the test checks the expected exception instead of passing on an unrelated failure.
Read the guide 4 min read
How do LIMIT and OFFSET work together to show one page of results?
See how SQLite combines ORDER BY, LIMIT, and OFFSET to return a specific page, with a runnable query and its exact sample output.
Read the guide 4 min read
How can I include or exclude several exact values with an IN filter?
Use IN to match any listed value and NOT IN to exclude them. In SQLite, NULL values need separate consideration.
Read the guide 4 min read
YOUR QUESTION. YOUR DETAILS.
Let’s work through your situation.
Get a tailored answer or help with your next task. Starts at $1, with a quote before you pay.