Search code examples
sql-serverms-access

Select all unique values in SQL table with additional information

I'm trying to find the first SeqGrp and SeqNum of the first unique values TaxGroupCode. I had this query in a MS Access database (shown below), but we recently started using SQL Server 2008 instead.

However, it seems FIRST isn't a valid function in T-SQL (at least in this version).

SELECT
    FIRST(DesotoCoPA_Trims.SeqGrp) & "-" &  FIRST(DesotoCoPA_Trims.SeqNum), "Tax District " & DesotoCoPA_Trims.TaxGroupCode AS Sample
FROM  
    DesotoCoPA_Trims
GROUP BY 
    DesotoCoPA_Trims.TaxGroupCode;

This produces:

GrpSeq  Sample
-----------------------
2-28    Tax District 2   
5-31    Tax District 3   
6-2123  Tax District 7   
6-2128  Tax District 1   
6-9694  Tax District 6

I'm relatively new to SQL, so I have no idea how to get around this problem. I don't need the results to be formatted this way specifically.

Solution

  • You need to get to a newer version of SQL Server. There are two reasons for this.

    One reason is SQL Server 2008 is hopelessly out of date. It hasn't had any patches or updates in more than 5 years... not even critical security updates. It's dangerous and iresponsible to still be using it. I understand you may not be in a position to make this change directly, but you likely are in a position to push this need (not want!) up the chain.

    The other reason is newer versions of SQL Server will let you easily solve this issue.

    To understand how, first I need to explain something about relational set theory. Real relational databases do not have any concept at all of the first or last item in a set. Absent an explicit ORDER BY directive they are free to return rows in any order they find convenient. This will commonly match the order stored on disk or from the primary key, but there are a number of things that can change this for a given run of a query. That is, a database can (and sometimes WILL) give different results from the same sql on the same data.

    That's not good. The First() function from Access violates this principle, and so was never added to most serious database products.

    Recent versions of SQL Server have something called Window Functions, one of which is First_Value(). This function is part of the ANSI standard, and makes use of an OVER clause where you will include a ORDER BY definition, so you can get deterministic results. This is the closest direct-match to the First() function from access.

    It's also often easier to use row_number() and then wrap the query so you can have a WHERE clause limit the results to rows where row_number() was 1 (this also replaces the GROUP BY clause).

    But these functions are not yet available in SQL Server 2008.

    Since we need to upgrade anyway, rather than talk about what version added the Window Function feature I'll talk about support and patching. From that lens, the minimum version of SQL Server you should be using today (July 2024) is SQL Server 2016 (formal end of life is July of 2026), but while you're at it you may as well go newer. For example, SQL Server 2022 should continue to get security updates into 2033.

    The good news is any database small enough to fit in MS Access is also likely to easily fit within the limits of the free Express Edition of a current SQL Server release. Cost isn't likely to be a barrier, even for commercial use, as long as the Access file wasn't pushing 10 GB. And if you can't install that because the operating system is too old, that's also a critical issue.