SQL2005.how can i perform this..? - 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)

SQL2005.how can i perform this..?

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2010-05-08 : 10:19:30

hi all,,
i am pulling some data from a db1 table t1(c1,c2,c3).
i have lot of fields with the same c1 but different c2,c3(date) .
what i need is to pullall distinct records c1,c2,c3 where c3 is the oldest date (MIN) and ignore the content of c2 ?

i.e :
c1 c2 c3
1 412 bla 1234
2 412 lol 1233

i need to pull only the record with the unique c1:412 and lowest c3:1233. the resukt inthis exmpl eshould be

412 lol 1233

thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 10:33:50

[code]SELECT c1, c2, c3
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY c3) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

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

korssane
Posting Yak Master

104 Posts

Posted - 2010-05-08 : 10:45:03

hey visakh,
thanks for the quick turnaround,,but i am getting 2 records as a result..
this is not what i want.?
i need all c1 with oldest c3 ?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 10:49:41

quote:
Originally posted by korssane

hey visakh,
thanks for the quick turnaround,,but i am getting 2 records as a result..
this is not what i want.?
i need all c1 with oldest c3 ?

thanks

sorry i got confused by scrambled format. i've edited my original post. use that modified suggestion

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

korssane
Posting Yak Master

104 Posts

Posted - 2010-05-08 : 11:08:26

hi visakkh,
thanks that was the right one..
but i still not able to understand the way row_number.. syntax ...works do you have any link or example for this specific function ?

also i have anothe problem if you do not mind ..!!
the data i have no includes c1,c2,c3. c3 is a date collumn.
i want to compare this table1 (c1,c2,c3) with another one table2 that has columns ( c11,c22,c33) ..same format as table1..
what i want is :
to look at the record with same c1 but in the last 6days before c3..
ion other way i want to look the record (c11,c22,c33) that has been created within the 6 days before (c1,c2,c3) ..
c33 shoul dbe included in the followingtime frame (c3 - 6 days)..

please let me know if you need more clarification..

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 11:12:26

see description of ROW_NUMBER here

http://msdn.microsoft.com/en-us/library/ms186734.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-08 : 11:15:54

for next part use


SELECT *
FROM table1 t1
CROSS APPLY (SELECT *
             FROM table2
             WHERE c11 = t1.c1
             AND c33 >= DATEADD(mm,-6,c3)
             )t2

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

korssane
Posting Yak Master

104 Posts

Posted - 2010-05-08 : 12:02:44

hi visakh , it is not working i am getting"
Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "SQL_Latin1_General_CI_AS" in the equal to operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-09 : 03:11:50

then you need use COLLATE DATABASE_DEFAULT for each of columns in comparison which are of type character. like:-


SELECT *
FROM table1 t1
CROSS APPLY (SELECT *
             FROM table2
             WHERE c11 COLLATE DATABASE_DEFAULT= t1.c1 COLLATE DATABASE_DEFAULT
             AND c33 >= DATEADD(mm,-6,c3)
             )t2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources