Need help on query - SQL Server Forums

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

Transact-SQL (2008)

Need help on query

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2012-01-28 : 12:03:26

Select col1, col2, createdon,... returns following records and I want to get only one record for each different col1 where createdon is the lastest(descending) among same col1 records. Can anyone help ? thanks.

col1 col2 createdon

abc 123 createdon
abc 456 createdon

def 789 createdon
def 101 createdon

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-28 : 13:17:17

Look up Row_Number. Partition by Col1, order by createdon desc and filter for where that is 1

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-28 : 17:47:30

another method


SELECT t1.*
FROM table t1
CROSS APPLY (SELECT MAX(createdon) AS latest
             FROM table
             WHERE col1= t1.col1
             )t2
WHERE t1.createdon = t2.latest

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2012-01-29 : 22:13:01

Thanks.

One thing is select statement is nested and getting syntax error when alias is set.
Below is my query

select ...
from ..
where .. in (select col from...where .. in (select ... ))
T
cross apply
(..)
T2
where ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 22:15:35

cross apply should be before where

ie


select ...
from .. T
cross apply
(..) T2
where ..
and .. in (select col from...where .. in (select ... ))

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2012-01-29 : 22:44:55

But select statement will complete with where clauses and it also contains join. Below is the query.

select v.new_visitId, new_programvisitId,p.new_Name,v.new_admissiondate
from new_visit v join new_program p on v.new_programvisitId = p.new_programId

where v.new_programvisitId in
(
select new_programId from new_program where new_referenceno in
(
select name from Connection where RelatedConnectionId in
(
select ConnectionId from Connection where Name = @param)))

T

CROSS APPLY (select MAX(new_admissiondate) AS latest
from new_visit v join new_program p on v.new_programvisitId = p.new_programId

where v.new_programvisitId in
(
select new_programId from new_program where new_referenceno in
(
select name from Connection where RelatedConnectionId in
(
select ConnectionId from Connection where Name = @param)))
)T2
WHERE T.new_admissiondate = T2.latest

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-29 : 22:50:39

why include it twice? just once is enough right?

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2012-01-29 : 23:07:23

I didnt get you. First select will return all records and second select will return latest admission date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 11:23:39

sorry i feel like you're complicating things
show us some sample data from table and what you need out of it and we will help you in getting it with much simpler query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources