Sql-server – How to count repeated rows taking into account other rows in the middle

sql serversql-server-2008-r2

For SQL server 2008 R2
It seems challenging to come up with a single query to the following:
Example given columns a & b:

a |   b
-------------
0 |  2000
1 |  2001
1 |  2002
1 |  2003
2 |  2004
3 |  2005
1 |  2006
1 |  2007
4 |  2008
1 |  2009

Goal: Mark rows with repeated column a and give them unique number taking into account other values in between. Result should be in column c. Note the most difficult part here is to populate column c with 2 & 5 & 7.

a |  b   |  c
-------------
0 |  2000 | 1
1 |  2001 | 2
1 |  2002 | 2
1 |  2003 | 2
2 |  2004 | 3
3 |  2005 | 4
1 |  2006 | 5
1 |  2007 | 5
4 |  2008 | 6
1 |  2009 | 7

Best Answer

This is a gaps-and-islands problem. One (of the many) ways to solve it (this requires 2012+ versions):

WITH 
  t AS
    ( SELECT a, b, x = CASE WHEN a = LAG(a) OVER (ORDER BY b) 
                           THEN NULL ELSE 1 
                       END
      FROM table_name
    )
SELECT a, b, c = COUNT(x) OVER (ORDER BY b) 
FROM t 
ORDER BY b ;

This should work in 2005 and above:

WITH 
  t AS
    ( SELECT a, b, dx = ROW_NUMBER() OVER (ORDER BY b) 
                        - ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) 
      FROM table_name
    ),
  tt AS
    ( SELECT a, b, mb = MIN(b) OVER (PARTITION BY a, dx)
      FROM t 
    )
SELECT a, b, c = DENSE_RANK() OVER (ORDER BY mb)
FROM tt 
ORDER BY b ;

Related Solutions

SQL Server Security Threats – Risks of SA and Known Account Names

Does an known account name like sa, pose a security threat to database?

A "god" user account with a known name is generally considered a worse idea than a god user with a less well known name. It makes brute force attacks that bit easier as the attacker only has to guess the password and not the username and the password.

Also having a god user anyway can be dangerous. You are generally better off having specific users with specific rights for what they need to do. This sort of privilege based security is easier to implement from scratch than it is to retrofit into your environment later.

Disabling sa and giving specific users specific admin rights as needed in SQL server is essentially the same recommendation as disabling root and handing out admin rights as needed via sudo under Linux and similar. You can always re-enable sa once directly connected to the machine with adequate privileges should anything go wrong and you end up dropping all the rights your users need to operate (and fix the issue) just the same as you can engineer root access to a Linux box if you have physical access to the box - so disabling the account is no magic bullet (but once an attacker has physical access to your machine, or full Administrative access via RDC or SSH, all bets are off anyway).

When using windows authentication on SQL Server does it impose the same password policy(if it was set to say account lockout after 5 times)?

When using Windows Integrated Authentication SQL server has no control over account lockouts and such - it just maps a Windows user to an SQL user and asks the OS to vouch for the fact that the user has provided appropriate credentials. For interactive human users this means any lockout would occur as the user attempted to authenticate with Windows, not as they logged in to SQL Server.

Sql-server – Query to normalize table/combine row text

This should work, I will clean it up later so its more efficient.

DECLARE @Old TABLE ( 
  id         INT, 
  rank       INT, 
  linenumber INT, 
  sometext   VARCHAR(1000)) 
DECLARE @New TABLE ( 
  id           INT, 
  rank         INT, 
  combinedtext VARCHAR(1000)) 


;WITH combinedresults(ctid, id, rank, linenumber, combinedtext) 
     AS (SELECT 0, 
                id, 
                rank, 
                linenumber, 
                CAST (sometext AS VARCHAR(8000)) 
         FROM   @Old o 
         WHERE  NOT EXISTS (SELECT TOP 1 1 
                            FROM   @Old 
                            WHERE  id = o.id 
                                   AND rank = o.rank 
                                   AND linenumber < o.linenumber) 
         UNION ALL 
         SELECT ctid + 1, 
                o.id, 
                o.rank, 
                o.linenumber, 
                ct.combinedtext + o.sometext 
         FROM   @Old o 
                INNER JOIN combinedresults ct 
                  ON ct.id = o.id 
                     AND ct.rank = o.rank 
         WHERE  o.linenumber > ct.linenumber) 

UPDATE n 
SET    combinedtext = ct.combinedtext 
FROM   @New n 
       INNER JOIN (SELECT n.id, 
                          n.rank, 
                          MAX(o.rank) orank 
                   FROM   @new n 
                          INNER JOIN @Old o 
                            ON n.id = o.id 
                               AND o.rank <= n.rank 
                   GROUP  BY n.id, 
                             n.rank) r 
         ON n.id = r.id 
            AND n.rank = r.rank 
       INNER JOIN (SELECT id, 
                          ct.rank, 
                          MAX(ctid) ctid 
                   FROM   combinedresults ct 
                   GROUP  BY ct.id, 
                             ct.rank) r2 
         ON r2.id = r.id 
            AND r2.rank = r.orank 
       INNER JOIN combinedresults ct 
         ON r.id = ct.id 
            AND ct.rank = r.orank 
            AND ct.ctid = r2.ctid 

SELECT * 
FROM   @New

Best Answer

Related Solutions

SQL Server Security Threats – Risks of SA and Known Account Names

Sql-server – Query to normalize table/combine row text

Related Question