select query without duplicates

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)

select query without duplicates

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-09 : 10:31:55

Dear all,

My table structure is like this..

id | name | EOD_ID | EOD_date| EDM_batch
1 a 222 201001 TC
1 b 203 0 TC

I need to select only one row even if there a non matching records for other fields except ID based on maximun EOD_date.
How can we select this max record from the above table even though there is non matching rows exist.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:39:14

Do you mean this?


select * from
(select
 row_number() over (partition by id order by EOD_date desc) as rownum,
 name, EOD_ID,EOD_date,EDM_batch
 from your_table)dt
where rownum=1

No, you're never too old to Yak'n'Roll if you're too young to die.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-09 : 10:43:37

becous i am using this query result to join some other table as well..is this okie withgood performance ??

I am using this query but still its not fetching the only one record.

select id
, name
,max(EOD_date) Creation_Date
,EDM_Batch
from TES_TRANSACTIONS
where EOD_ID is not null
or EOD_date <> 0
or EDM_batch <> 0
or id <> 0
or name <> 0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-09 : 10:54:46

Your query gives the max(EOD_date) from the table regarding the where clause but with no relation to the id or other columns.
About performance I can't say something without any information.
Did you try my solution?

No, you're never too old to Yak'n'Roll if you're too young to die.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-02-09 : 11:31:29

In your query i need max EOD_date ??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 11:40:57

[code]
select t.*
from table t
inner join (select id,max(EOD_date) AS Recent
from table
group by id)t1
on t1.id=t.id
and t1.Recent = t.EOD_date
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources