Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping

Author

Topic

gad
Starting Member

14 Posts

Posted - 2006-01-30 : 21:34:24

I have the following table and I'm trying to identify sessionid's where the category names in one searchcriteriaid group have changed within in the same session. In the example below, Land, Multifamily, Retail, Shopping Center are all consistent until the last searchcriteriaid group (60720021), so this sessionid should return. Thanks for any suggestions.
SearchCriteriaid SessionID Category
60719347 55850633 Land
60719347 55850633 Multifamily
60719347 55850633 Retail
60719347 55850633 Shopping Center
60719548 55850633 Land
60719548 55850633 Multifamily
60719548 55850633 Retail
60719548 55850633 Shopping Center
60719625 55850633 Land
60719625 55850633 Multifamily
60719625 55850633 Retail
60719625 55850633 Shopping Center
60719717 55850633 Land
60719717 55850633 Multifamily
60719717 55850633 Retail
60719717 55850633 Shopping Center
60719719 55850633 Land
60719719 55850633 Multifamily
60719719 55850633 Retail
60719719 55850633 Shopping Center
60719788 55850633 Land
60719788 55850633 Multifamily
60719788 55850633 Retail
60719788 55850633 Shopping Center
60720021 55850633 Land
60720021 55850633 Multifamily
60720021 55850633 Industrial
60720021 55850633 Office

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-30 : 23:07:55

select SearchCriteriaid
from tbl t1
where Category not in (select t2.Category from tbl t2 where t2.SearchCriteriaid = (select min(t3.SearchCriteriaid) from tbl t3 where t2.SessionID = t1.SessionID))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gad
Starting Member

14 Posts

Posted - 2006-01-31 : 20:08:19

Previous suggestion worked well (thanks a lot), but only when there was a distinct sesssionid, my full set of data has many differnt sessionids - how would I accomodate for that? Thanks.

searchcriteriaid sessionid category
60658874 55756828 Land
60658874 55756828 Multifamily
60658961 55756828 Land
60658961 55756828 Multifamily
58040575 51478216 Office
58040576 51478216 Retail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-31 : 21:23:13

oops
select SearchCriteriaid
from tbl t1
where Category not in (select t2.Category from tbl t2 where t2.SearchCriteriaid = (select min(t3.SearchCriteriaid) from tbl t3 where t3.SessionID = t1.SessionID))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gad
Starting Member

14 Posts

Posted - 2006-02-02 : 16:06:57

Brilliant - thanks nr - much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources