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.
Author |
Topic |
satheesh
Posting Yak Master
152 Posts |
Posted - 2012-11-14 : 10:36:42
|
Dear AllI tried to compare and pull all the date from the same table but i stuck with error!!and looking for your help!!I need to compare the data from the same field based on few condition and need to pull all the corresponding data i.efor eg:name position price date LLL 2 11.19 2012-11-14MSN 3 12.21 2012-11-14GPW 1 10.19 2012-11-14DRS 5 14.67 2012-11-14YAH 2 15.19 2012-11-13GPW 1 14.19 2012-11-13KLN 7 21.19 2012-11-13Here i want to pull all the data of our competitor who is second ('2') position when 'GPW' appears is in position '1' and need to display row by row based on dateResult name position price dateGPW 1 10.19 2012-11-14LLL 2 11.19 2012-11-14GPW 1 14.19 2012-11-13YAH 2 15.19 2012-11-13Any help will be highly appreciated!ThanksRegards,SG |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-11-14 : 10:58:58
|
; WITH GPW AS( SELECT t1.NAME,t1.POSITION,t1.Price,t1.[Date]FROM yourTable t1 WHERE t1.[Name] = 'GPW' AND t1.POSITION = 1)SELECT * FROM GPWUNION SELECT t1.* FROM @TABLE t1 INNER JOIN GPW ON t1.Position = 2 AND gpw.[date] = t1.[Date]JimEveryday I learn something that somebody else already knew |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 05:54:47
|
[code]SELECT NAME,POSITION,PRICE,[DATE]FROM(SELECT *,MIN(CASE WHEN POSITION = 1 THEN NAME ELSE NULL END) OVER (PARTITION BY [DAte]) AS MinPosFROM Table)tWHERE POSITION <=2AND MinPos='GPW'ORDER BY [DATE],POSITION[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|