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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -