comapre two column - 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

General SQL Server Forums

Script Library

comapre two column

Author

Topic

neeraj1401
Starting Member

36 Posts

Posted - 2013-04-01 : 10:27:32

I’m having one table having two column ID and Segment.

Create table test
(id numeric ,
segment numeric,
created_on datetime)

insert into test values (50708520,1,getdate())
insert into test values (50708520,2,getdate())
insert into test values (50708520,3,getdate())
insert into test values (50708520,1,getdate())
insert into test values (50708521,1,getdate())
insert into test values (50708522,1,getdate())
insert into test values (50708523,1,getdate())
insert into test values (50708523,1,getdate())
insert into test values (50708524,1,getdate())
insert into test values (50708524,2,getdate())
insert into test values (50708524,1,getdate())

I want a query/procedure which will found id which have last and first segment matching for that ID on the basis of created_on.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-01 : 10:59:39

Your explanation is not 100% clear to me, but something like this?

select
	ID,
	segment_numeric,
	created_on,
	CASE WHEN FirstId = 1 then 'First' else 'Last' end as RankNumber
from
(
	select
		RANK() over (partition by id order by created_on asc) as FirstId,
		RANK() over (partition by id order by created_on desc) as LastId,
		*
	from
		Test
)s
where FirstId = 1 or LastId = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 11:52:54

If SQL 2012


SELECT *
FROM (
SELECT *,
FIRST_VALUE(segment) OVER (PARTITION BY id ORDER BY created_on) AS First,
LAST_VALUE(segment) OVER (PARTITION BY id ORDER BY created_on) AS Last
FROM Table
)t
WHERE First=Last

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 13:01:21

If below 2012 use this


;With CTE
AS
(
SELECT id
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_on) AS Seq,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_on DESC) AS BSeq
FROM Table
)t
WHERE Bseq=1
OR Seq=1
GROUP BY id
HAVING COUNT(DISTINCT segment) =1
)


SELECT t.*
FROM Table t
JOIN CTE c
ON c.id = t.id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources