Need a 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 2005 Forums

Transact-SQL (2005)

Need a Query

Author

Topic

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-22 : 08:26:03

I have one lookup table and one main table
in main table structure is
ID, Date, Status

lookup table structure is
ID, Date
lookup table contains latest fail status entry for each id

In main table id may be muliple times but not in lookup

I want to get distinct ID , Date, Status from main table
with the latest date from lookup for that perticular ID
but its possible i may not have entry for some id in lookup table
because if status in success then entry will not go into lookup table
even i want to get the data from main table and date also that might be success date or failure date.

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:35:06

can you show some data and then explain? is it that you always want latest date record either from main or lookup?

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-22 : 08:38:10

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:41:32

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T


SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

EDIT:corrected typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 08:44:09

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

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

Seems visakh made small typo error

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-22 : 08:48:52

quote:
Originally posted by Idera

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

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

Seems visakh made small typo error

PBUH

just corrected it and refresh and saw that you've already spotted it
Nice catch

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-22 : 09:15:03

again there is error of alias of inner query result set is missing

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
) a
WHERE Seq=1

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-22 : 09:21:33

But thanks Visakh,

Its really funtastic query it will help me a lot....

Vabhav T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 09:38:25

Also see how effectively you can make use row_number() function for various purposes
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 09:40:59

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

EDIT:corrected typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Visakh you seems to have stopped using Apply clauses lately



SELECT ID,Date,Status
FROM
(
SELECT t.id,t.date,t.status,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq from main t,
OUTER APPLY(Select top 1 id from lookup  l where l.id=t.id order by l.id)tbl
)
WHERE Seq=1

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-22 : 09:43:26

quote:
Originally posted by Idera

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

EDIT:corrected typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Visakh you seems to have stopped using Apply clauses lately

SELECT ID,Date,Status
FROM
(
SELECT t.id,t.date,t.status,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq from main t,
OUTER APPLY(Select top 1 id from lookup l where l.id=t.id order by l.id)tbl
)
WHERE Seq=1

PBUH

He is more specilised in CROSS-APPLY

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-02-22 : 10:34:48

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

quote:
Originally posted by visakh16

quote:
Originally posted by vaibhavktiwari83

Yes i want latest date record from either from main or lookup. but if it is in lookup table then it should show from look up only if its not then from main table.

Vabhav T

SELECT ID,Date,Status
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY COALESCE(l.ID,t.ID) ORDER BY COALESCE(l.Date,t.Date) DESC) AS Seq,
COALESCE(l.ID,t.ID) AS ID,COALESCE(l.Date,t.Date) AS DAte,
t.Status
FROM main t
LEFT JOIN lookup l
ON l.ID=t.ID
)
WHERE Seq=1

EDIT:corrected typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Visakh you seems to have stopped using Apply clauses lately

SELECT ID,Date,Status
FROM
(
SELECT t.id,t.date,t.status,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS Seq from main t,
OUTER APPLY(Select top 1 id from lookup l where l.id=t.id order by l.id)tbl
)
WHERE Seq=1

PBUH

He is more specilised in CROSS-APPLY

Madhivanan

Failing to plan is Planning to fail

Maybe that explains why..

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources