How to get 1 record from more than one record?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to get 1 record from more than one record??

Author

Topic

getur.srikanth@gmail.com
Yak Posting Veteran

77 Posts

Posted - 2009-10-07 : 11:18:30

[code]select c2.constit_id, t.CODE_DESC, c2.ADD_DT
from me.cncatcod c2, ME.CNCODTAB t where
t.cat_code = 'SCLVL1PRIM'
and c2.cat_code = t.cat_code
and c2.code_value = t.code_value
and c2.constit_id='1691047'
--and rownum =1
group by c2.constit_id, t.CODE_DESC, c2.ADD_DT
order by c2.ADD_DT desc[/code]

How can get only one record from the latest added dates from them. Ex: I have 4 records 2/5/2008 and 1 record on 3/8/2002. But I want get 1 record from that 4 records.

Some time I may have 2 on same date and 1 is on old date.

asgast
Posting Yak Master

149 Posts

Posted - 2009-10-07 : 11:46:54

try this

SELECT * FROM (select c2.constit_id, t.CODE_DESC, c2.ADD_DT, ROW_NUMBER() OVER (ORDER BY c2.ADD_DT DESC) AS rownumber
from me.cncatcod c2, ME.CNCODTAB t where
t.cat_code = 'SCLVL1PRIM'
and c2.cat_code = t.cat_code
and c2.code_value = t.code_value
and c2.constit_id='1691047') p
WHERE p.rownumber =1

this is not the best solution, also its 2009 maybe you should start using JOINS?

Can't fall asleep, the ghost of sql server will get me for writing bad queries. Have to stay awake ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources