Solved

# sql query

Posted on 2016-09-14
Medium Priority
86 Views
how to frame a sql query to select minimum first 20 records in week and like that need to select for all the week in year with a single query.

0
Question by:Jayaprakash M
• 8
• 7
• 4
• +3

LVL 35

Expert Comment

ID: 41797535
When you say minimum first 20 do you mean ignore the week if there's less than 20 and return them all for the week if there's more than 20?
0

Author Comment

ID: 41797538
When the records sorted by one of the column, if it having even 1 record also it should display.
I mean when the week records sorted by one column, just trying to collect only first 20 records.
0

LVL 49

Expert Comment

ID: 41797543
select up to the first 20 records in week for all the weeks in year with a single query

Does that describe your need?

How is "first" determined? By a date/time column?
0

Author Comment

ID: 41797549
when the records are sorted by using one of the column, then will fetch only first 20 records
0

LVL 11

Assisted Solution

HuaMinChen earned 500 total points (awarded by participants)
ID: 41797564
Hi,
You need to loop all the weeks per given here. Here is way to get start date and end date per relevant year and week number.

``````declare @yr int,
@week int,
@month nvarchar(2),
@start_dt datetime;
set @yr=2013
set @week=6
set @month='03'
set @start_dt=convert(datetime,ltrim(rtrim(cast(@yr as varchar)))+@month+'01',112)

select case when @week=1 then @start_dt else dateadd(dd,(@week-1)*7+8-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end-7,@start_dt) end [1st Day of Week],case when dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end,@start_dt)>=dateadd(mm,1,@start_dt) then dateadd(dd,-1,dateadd(mm,1,@start_dt)) else dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end,@start_dt) end [Last Day of Week]
``````
it means you have to further loop for all weeks.
0

Author Comment

ID: 41797576
Hi Chen, thanks a lot for the quick solution. But date we are not suppose to hard code, that should be sysdate and the query should fetch the entire year data with sorted by one of the column in week and only to fetch first 20 records. So that it is easy to compare the data.

Thanks.
0

LVL 14

Assisted Solution

Nakul Vachhrajani earned 500 total points (awarded by participants)
ID: 41797580
In such cases, sample data would have helped a lot.

My understanding is that you have data that spans a few years. You want the first 20 records from each week.

Here's something that you can try:

``````USE AdventureWorks2014;
GO
--Table to hold the sample data
DECLARE @dataByWeekNumber TABLE (RecordId    INT          NOT NULL IDENTITY(1,1),
RecordDate  DATE         NOT NULL,
RecordValue VARCHAR(255)     NULL
);

--Prepare some test data
INSERT INTO @dataByWeekNumber (RecordDate, RecordValue)
SELECT so.create_date AS RecordDate,
so.name AS RecordValue
FROM [sys].[objects] AS so;

--Fetch the 1st 20 records per week
;WITH RowNumberByWeek (RecordId, RecordDate, RecordValue, WeekNumber, RowNumberByWeek)
AS (SELECT dwn.RecordId,
dwn.RecordDate,
dwn.RecordValue,
DATEPART(WEEK,dwn.RecordDate) AS WeekNumber,
ROW_NUMBER() OVER (PARTITION BY DATEPART(WEEK,dwn.RecordDate) ORDER BY dwn.RecordDate, dwn.RecordId)
FROM @dataByWeekNumber AS dwn
)
SELECT rbw.RecordId,
rbw.RecordDate,
rbw.RecordValue,
rbw.WeekNumber,
rbw.RowNumberByWeek
FROM RowNumberByWeek AS rbw
WHERE rbw.RowNumberByWeek <= 20;
``````
1

LVL 49

Expert Comment

ID: 41797591
In such cases, sample data would have helped a lot.
and the "expected result" too.

Providing both sample data and expected result is the most effective way to ask a sql query related question.
0

Author Comment

ID: 41797597
thank a lot Vachhrajani. Let me try this and let you the results.
0

LVL 38

Expert Comment

ID: 41797698
@Jayaprakash M - Do you need more help on this ?
0

Author Comment

ID: 41797716
Hi Pavan, we are trying on above solutions, if you have any better solution please provide me.
Thanks.
0

LVL 38

Expert Comment

ID: 41797721
Yes sir, working on a solution for you ! you will get the same in some time.
0

LVL 38

Assisted Solution

Pawan Kumar earned 1000 total points (awarded by participants)
ID: 41797767
Best approach..........Enjoy !!

``````DECLARE @dataByWeekNumber TABLE (RecordId    INT          NOT NULL IDENTITY(1,1),
RecordDate  DATE         NOT NULL,
RecordValue VARCHAR(255)     NULL
);

--Prepare some test data
INSERT INTO @dataByWeekNumber (RecordDate, RecordValue)
SELECT so.create_date AS RecordDate,
so.name AS RecordValue
FROM [sys].[objects] AS so;

;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) rb
from
SingleDigits as d1,
SingleDigits as d2
)
SELECT r.*,p.rb WeekNumber FROM Series p
CROSS APPLY
(
SELECT TOP 20
RecordId,
RecordDate,
RecordValue
FROM @dataByWeekNumber rb
where p.rb = DATEPART(WEEK,rb.RecordDate)
)r
``````
0

Author Comment

ID: 41797782
Thanks a lot Pavan. Will try and let you know the results.
0

LVL 38

Expert Comment

ID: 41797785
Great ! thanks bro !
0

Author Comment

ID: 41797819
Here Table is already having with more than 1 million records.

Assume that table name is emp, and column name need to be sorted with 'total' weekly, for whole year. In that case kindly simplify the query.

Thank lot in advance.
0

LVL 38

Accepted Solution

Pawan Kumar earned 1000 total points (awarded by participants)
ID: 41797901
Here it is

``````;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) rb
from
SingleDigits as d1,
SingleDigits as d2
)
SELECT r.*,p.rb total FROM Series p
CROSS APPLY
(
SELECT TOP 20
RecordId,
RecordDate,
RecordValue
FROM emp rb
where p.rb = DATEPART(WEEK,rb.RecordDate)
)r
``````
0

LVL 14

Expert Comment

ID: 41797932
If you have a large number of rows, the CROSS APPLY in the proposed solution (ID: 41797901) will generate a large number of logical reads (since the inner statement will be need to be executed, i.e. "applied" for each row in the outer CTE).

Suggest you perform a performance evaluation of both solutions.
0

LVL 49

Expert Comment

ID: 41798886
Be wary of claims such as "best solution" particularly if very few facts have been provided in the question.

Be wary of claiming a "best solution" until sufficient facts are known.

A single pass of the data is most likely to produce the best performance. So I recommend you try using the ROW_NUMBER() OVER () approach as it only requires a single pass through the data.
0

LVL 38

Expert Comment

ID: 41799056
@Nakul Vachhrajani - In most of the cases CROSS APPLY will perform better as compared to inner join. Also ORDERING an entire data set is more costly the ordering chunks of data..

@author - compare the timings and stats before you implement any solution ! Enjoy
0

LVL 38

Expert Comment

ID: 41803743
@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.
0

LVL 14

Expert Comment

ID: 41803827
The performance of any query depends upon the nature of the data structures, underlying data and the query itself. Hence, to say that CROSS APPLY will generally perform better may not be correct. CROSS APPLY works great when the output of the applied function depends upon the value of the rows in the outer query (i.e. the function needs to be calculated for, i.e. "applied to" each row separately). If this is not the case, a JOIN may be faster.

Also, when using TOP, one has to use ORDER BY. Without ORDER BY, the order of the output rows is not guaranteed.

Finally depending on the size of the data, I would rather take the performance hit of ordering once rather than once with each outer row (since SQL may need to spool out to the tempdb for the sorting and IO is always the costliest operation).
0

LVL 49

Expert Comment

ID: 41804005
I suggest that the person claiming "best" should prove that claim, or refrain from making claims they are unwilling to prove.

There is no compelling need for cross apply in solving this question and a cross apply is not more efficient than an inner join (if that was true database vendors would not use inner joins would they?). The apply operator is a very valuable feature but it is not the only worthwhile feature.
0

LVL 38

Expert Comment

ID: 41804158
@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.
0

LVL 11

Expert Comment

ID: 41833331
No further update from author while there is enough advice given to author.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that witâ€¦
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month14 days, 19 hours left to enroll

#### 839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.