July 15, 2011 at 12:20 am
I have a query where i am suing inline query which will take the latest data from table
" select username from table where conditions>22 order by column1 desc"
i am using like this :
select distinct col1,col2,(select top 1 username from table where conditions>22 order by column1 desc) as col3,
(select top 1 names from table where conditions>22 order by column1 desc) as col4.... from table1
UNION
select col1,col2,(select top 1 username from table where conditions>22 order by column1 desc) as col3,
(select top 1 names from table where conditions>22 order by column1 desc) as col4.... from table2
because of the above query I am getting the result very slow, it take lot of time to get result.
Will joins work? if yes then how can i use in place of inline queries since inline queries give me top 1 latest data because of
order by desc use.
I want some alternative to this inline queries.
July 15, 2011 at 2:14 am
Please post table def and sample data in a ready to use format as described in the first link in my signature.
Also, please include your expected result based on the sample data.
July 15, 2011 at 4:04 am
Check the attached txt file for design and query to be edited.
July 15, 2011 at 4:14 am
Please include sample data and expected/desired results too.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 15, 2011 at 9:42 am
What is the business logic behind it?
All of the subqueries use a TOP 1 without an ORDER BY. What's the purpose?
Why not simply using the following approach (example of one part of the UNION statement):
SELECT
[Credit Note NO],
[Department],
MIN([Designation]) AS [Pending WITH Designation],
MIN([UserName]) AS [Pending WITH],
DATEDIFF(DD, MAX([Modified]), GETDATE()) AS [Bucket]
FROM dbo.App1
WHERE [Status] = 'Pending'
GROUP BY [Credit Note NO],[Department]
You might also want to verify if UNION is really needed and you cannot use UNION ALL.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply