Need help in retrieving distinct from duplicate co

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)

Need help in retrieving distinct from duplicate co

Author

Topic

sgcool
Starting Member

5 Posts

Posted - 2006-11-10 : 02:32:33

My data is like Below:

id abc def
---- --------- ------------------------------
1 1.584795 2006-11-04 13:36:06.0
2 1.584795 2006-11-04 14:03:09.0
3 1.584795 2006-11-04 17:42:31.0
4 1.584795 2006-11-05 03:13:23.0
5 1.598802 2006-11-05 08:53:48.0
6 1.598802 2006-11-05 09:25:50.0
7 1.598802 2006-11-05 09:35:49.0
8 1.598802 2006-11-05 12:53:43.0

here columns id, abc, def

i want only rows with id 4, 8. these are latest updated rows in to table based on date. Here column abc is having duplciate values with distinct date/time value.

I appriciate for any help using sql queries. i tried, but am poor in SQL.
can any body pls help me...

Rgds,
sgcool.

--------------------------------------------------------------------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-10 : 02:43:50

[code]
create table #temp
(
id int,
abc float,
def datetime
)

insert into #temp
select 1, 1.584795, '2006-11-04 13:36:06.0' union all
select 2, 1.584795, '2006-11-04 14:03:09.0' union all
select 3, 1.584795, '2006-11-04 17:42:31.0' union all
select 4, 1.584795, '2006-11-05 03:13:23.0' union all
select 5, 1.598802, '2006-11-05 08:53:48.0' union all
select 6, 1.598802, '2006-11-05 09:25:50.0' union all
select 7, 1.598802, '2006-11-05 09:35:49.0' union all
select 8, 1.598802, '2006-11-05 12:53:43.0'

select *
from #temp t
where def = (select max(def) from #temp x
where x.def >= dateadd(day, datediff(day, 0, t.def), 0)
and x.def < dateadd(day, datediff(day, 0, t.def), 1)
)
[/code]

Should be id 3 and 8 ?

sgcool
Starting Member

5 Posts

Posted - 2006-11-10 : 03:49:05

If possible help me in Oracle please..

here i need both abc,def columns as result.

Sorry to troubling you..

Rgds,
sgcool..

sgcool
Starting Member

5 Posts

Posted - 2006-11-10 : 03:52:06

Hi KhTan,
thanks for information. but iam in bit confusion as am doing in oracle...

and i need only 4 and 8 rows as 4 is latest than 3 and 8 also latest for abc = '1.598802'

my result should be

id abc def
---- ------ --------
4 1.584795 2006-11-05 03:13:23.0
8 1.598802 2006-11-05 12:53:43.0

Rgds,
prasad.CH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:03:52

Do you want latest per date or latest per id (which i imagine is an identity column)?

Peter Larsson
Helsingborg, Sweden

sgcool
Starting Member

5 Posts

Posted - 2006-11-10 : 04:15:04

Hi Peter,
thanks., i need latest by date not id.

thanks.,
Rgds,
sgcool

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:19:39

[code]create table #temp
(
id int,
abc float,
def datetime
)

insert into #temp
select 1, 1.584795, '2006-11-04 13:36:06.0' union all
select 2, 1.584795, '2006-11-04 14:03:09.0' union all
select 3, 1.584795, '2006-11-04 17:42:31.0' union all
select 4, 1.584795, '2006-11-05 03:13:23.0' union all
select 5, 1.598802, '2006-11-05 08:53:48.0' union all
select 6, 1.598802, '2006-11-05 09:25:50.0' union all
select 7, 1.598802, '2006-11-05 09:35:49.0' union all
select 8, 1.598802, '2006-11-05 12:53:43.0'

select t.*
from #temp t
inner join (
select abc,
max(def) md
from #temp
group by abc
) q on q.abc = t.abc and q.md = t.def

drop table #temp[/code]

Peter Larsson
Helsingborg, Sweden

sgcool
Starting Member

5 Posts

Posted - 2006-11-10 : 04:57:15

Hi Peter,
thanks for solution. but unfortunately, my data may get like

1, 1.584795, '2006-11-04 13:36:06.0'
2, 1.584795, '2006-11-04 14:03:09.0'
3, 1.584795, '2006-11-04 17:42:31.0'
4, 1.584795, '2006-11-05 03:13:23.0'
5, 1.598802, '2006-11-05 08:53:48.0'
6, 1.598802, '2006-11-05 09:25:50.0'
7, 1.598802, '2006-11-05 09:35:49.0'
8, 1.598802, '2006-11-05 12:53:43.0'
9, 1.584795, '2006-11-05 12:53:49.0'

in above case id 9 row have another value at diff time. so i cannot use group by clause of abc. my main aim is get distinct abc values at different and latest timings. bit tricky one..

thanks

Rgds,
sgcool

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:11:22

This is not as per required in the original posting!!
What you want is MAX (latest) id for every abc in sequence def?

In your example above, you want rows 4 (1-3 omitted since not last in this sequence), 8 (5-7 omitted since not last in this sequnce) and 9?

LEARN HOW TO EXPLAIN WHAT YOU REALLY WANT THE FIRST TIME!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 06:10:50

Try this

create table #temp
(
	id 	int,
	abc 	float,
	def	datetime
)

insert into #temp
select	1, 1.584795, '2006-11-04 13:36:06.0' union all
select	2, 1.584795, '2006-11-04 14:03:09.0' union all
select	3, 1.584795, '2006-11-04 17:42:31.0' union all
select	4, 1.584795, '2006-11-05 03:13:23.0' union all
select	5, 1.598802, '2006-11-05 08:53:48.0' union all
select	6, 1.598802, '2006-11-05 09:25:50.0' union all
select	7, 1.598802, '2006-11-05 09:35:49.0' union all
select	8, 1.598802, '2006-11-05 12:53:43.0' union all
select	9, 1.584795, '2006-11-05 12:53:49.0'


select		t1.*
from		#temp t1
left join	#temp t2 on t2.id -1 = t1.id
where		t2.id is null
		or t2.abc <> t1.abc

drop table #temp

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources