Need help to return unique row

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 2005 Forums

Transact-SQL (2005)

Need help to return unique row

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-18 : 12:09:08

I've tables and rows as follow,

declare @t1 table
(idx smallint identity(1,1),timerHidx smallint,trouthidx smallint,
seq tinyint,dprtdte smalldatetime,dprttime smalldatetime);
/*@t1(timerHidx,trouthidx,seq,dprtdte,dprttime) is unique*/

insert into @t1 values(2,1,1,'2010-03-20 00:00:00','1900-01-01 10:30:00');
insert into @t1 values(2,1,1,'2010-04-07 00:00:00','1900-01-01 10:30:00');
insert into @t1 values(2,1,1,'2010-04-08 00:00:00','1900-01-01 10:30:00');
insert into @t1 values(7,2,1,'2010-03-18 00:00:00','1900-01-01 09:00:00');
insert into @t1 values(7,2,1,'2010-03-18 00:00:00','1900-01-01 14:30:00');
insert into @t1 values(8,2,2,'2010-04-10 00:00:00','1900-01-01 16:30:00');
insert into @t1 values(8,2,2,'2010-04-10 00:00:00','1900-01-01 11:00:00');

How to return unique row as follow,

idx   | timerHidx | troutehidx | seq | dprtdte
--------------------------------------------------------------
1	    2	        1	         1	   2010-03-20 00:00:00
2	    2	        1	         1	   2010-04-07 00:00:00
3	    2	        1	         1	   2010-04-08 00:00:00
4	    7	        2	         1	   2010-03-18 00:00:00
6	    8	        2	         2	   2010-04-10 00:00:00

I dont care the idx value

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-18 : 12:14:00

You mean a distinct?

select distinct timerhidx, trouthidx, seq, dprtdte from @t1

How to ask good questions that get answers:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Ray Dai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:20:27

[code]SELECT idx ,timerHidx ,troutehidx , seq ,dprtdte
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY timerHidx , troutehidx , seq , dprtdte ORDER BY idx) AS RowNo,
idx ,timerHidx ,troutehidx , seq ,dprtdte
FROM Table
)t
WHERE RowNo=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-03-18 : 12:53:06

tq sir. me still weak to using over partition

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:58:29

learn it by looking at books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources