Sql-server – Query Rows That Have a Following Row That Meets Criteria

join;sql server

Will try to do my best to explain this, as I don't really know how to word this to search for an answer. I'm a constantly learning but still newish SQL user who thus far has been able to solve most questions by searching, but can't think how to word this.

I want to pull a query, which is all contained in one single table. The database is logging rows as follows;

User    Login Type

Bob        1

Ryan       1

Tom        1

Tom        3

I want the query to show me the rows that have a "1" login type immediately followed by a "3" login type. So my query would result would look like this:

User  Login Type

Tom    1
Tom    3

How would I format such a query? Normally something like this would be easy by doing a few joins, but I can't seem to wrap my head around how to do it with only one table involved. I've looked into joining the table into itself, but can't seem to nail down how to get the results I want (if that is even what I should be doing, it is the only thing that seems logical to me)

Best Answer

I've used MySQL, but it should apply to virtually all database servers.

CREATE TABLE `test1` 
(
  `user` varchar(10) DEFAULT NULL,
  `logintype` int(11) DEFAULT NULL
);



mysql> SELECT * FROM test1;
+------+-----------+
| user | logintype |
+------+-----------+
| Bob  |         1 |
| Ryan |         1 |
| Tom  |         1 |
| Tom  |         3 |  <<== ALSO WORKS IF YOU HAVE ANOTHER 
+------+-----------+       RECORD TOM, 5
4 rows in set (0.00 sec)

mysql> 

SELECT t1.user, t1.logintype  
FROM test1 t1 
INNER JOIN test1 t2  
ON t1.user = t2.user 
AND t1.logintype != t2.logintype 
GROUP BY t1.user, t1.logintype  
ORDER BY logintype;    

+------+-----------+
| user | logintype |
+------+-----------+
| Tom  |         1 |
| Tom  |         3 |
+------+-----------+

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

SQL Server Pivot – How to Convert Query Results into Groups of Common Results

Seems a simpler approach:

SELECT GroupValue = Val, [Count] = COUNT(DISTINCT MyGroup) FROM 
(
  SELECT MyGroup, Val = STUFF((SELECT ', ' + RTRIM(MyValue) 
   FROM dbo.MyTable WHERE MyGroup = t.MyGroup FOR XML PATH('')), 1, 2, '')
  FROM dbo.MyTable AS t
) AS x GROUP BY Val;

Best Answer

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

SQL Server Pivot – How to Convert Query Results into Groups of Common Results

Related Question