background preloader

Transact SQL

Facebook Twitter

How to select the first/least/max row per group in SQL at Xaprb. Thu, Dec 7, 2006 in Databases Here are some common SQL problems, all of which have related solutions: how do I find the most recent log entry for each program?

How to select the first/least/max row per group in SQL at Xaprb

How do I find the most popular item from each category? How do I find the top score for each player? In general, these types of “select the extreme from each group” queries can be solved with the same techniques. I’ll explain how to do that in this article, including the harder problem of selecting the top N entries, not just the top 1. This topic is related to numbering rows, which I just wrote about (see my articles about MySQL-specific and generic techniques to assign a number to each row in a group). Selecting the one maximum row from each group Let’s say I want to select the most recent log entry for each program, or the most recent changes in an audit table, or something of the sort. There are a few common solutions to this problem.

One common solution is a so-called self-join. Select the top N rows from each group Yuck! The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code) So here I am writing even more documentation for my current Gig, and thinking once again, why not post it to OraFAQ and get double duty out of the document.

The SQL WITH Clause, (Order Your Thoughts, Reuse Your Code)

So here is a discussion of the WITH clause that comes with the SELECT statement now. It is easy to use, and handy as all get out, but seems many people have not yet noticed it. Hmm... a SELECT statement that does not start with SELECT. I like examples as a learning tools, so lets start off with some seemingly silly code. col dummy format a10 select dummy from dual / DUMMY ---------- X 1 row selected. So where is this going?. WITH stupid_is_as_stupid_does as ( select dummy from dual ) select * from stupid_is_as_stupid_does / DUMMY ---------- X 1 row selected. What just happened? So what we are saying is, conceptually, the WITH clause allows us to give names to predefined SELECT statements inside the context of a larger SELECT statement. From these four SELECT statements we learn that the WITH clause lets us do the following: Kevin. SQL Server CTE Basics. The CTE was introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable.

SQL Server CTE Basics

For some reason, it can be difficult to grasp the techniques of using it. Well, that's before Rob Sheldon explained it all so clearly for us. Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs—recursive and nonrecursive. Working with Common Table Expressions You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. [WITH <common_table_expression> [,...]] <common_table_expression>::= cte_name [(column_name [,...])] AS (cte_query) ...which can be represented like this... sp.City,