Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


interesting query


interesting query

Author
Message
earbash
earbash
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 130
I wonder if anyone can help with this query which I cant figure out?
I want to limit the amount of rows (or groups) returned, it could be any number but in this case lets say 8 rows, the problem is that it must include all items within 'grp'. The results are to be ordered in the same order as entered into the table. ie dte, 'grp'.

So, my example of returning 8 rows returns everything else correctly BUT would return only half of the '445' group. How can I construct a query that has some kind of look ahead and continues to return rows until all items with the 'grp' are returned (in this case it should actually return 13 rows?

I'm pretty sure CTE is the answer, but cant work out how.

Many thanks in advance, your assistance would be much appreciated.


declare @tbl table (id int identity(1,1), grp int, dte datetime) -- live data contains many additional columns

INSERT @tbl values (1, '20 jan 2011')
INSERT @tbl values (1, '21 jan 2011')
INSERT @tbl values (2, '22 jan 2011')
INSERT @tbl values (2, '22 jan 2011')
INSERT @tbl values (445, '26 jan 2011')
INSERT @tbl values (445, '26 jan 2011')
INSERT @tbl values (445, '26 jan 2011')
INSERT @tbl values (445, '27 jan 2011')
INSERT @tbl values (445, '27 jan 2011')
INSERT @tbl values (445, '28 jan 2011')
INSERT @tbl values (445, '28 jan 2011')
INSERT @tbl values (445, '28 jan 2011')
INSERT @tbl values (445, '29 jan 2011')
INSERT @tbl values (6, '1 feb 2011')
INSERT @tbl values (8, '3 feb 2011')
INSERT @tbl values (1, '30 mar 2011')
INSERT @tbl values (1, '30 mar 2011')
INSERT @tbl values (1, '30 mar 2011')

;
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER
(PARTITION BY grp
ORDER BY dte ASC, grp)
AS RowNumber
FROM @tbl tbl
)
SELECT * FROM CTE
order by dte, grp



1 1 2011-01-20 00:00:00.000 1
2 1 2011-01-21 00:00:00.000 2
3 2 2011-01-22 00:00:00.000 1
4 2 2011-01-22 00:00:00.000 2
5 445 2011-01-26 00:00:00.000 1
6 445 2011-01-26 00:00:00.000 2
7 445 2011-01-26 00:00:00.000 3
8 445 2011-01-27 00:00:00.000 4
9 445 2011-01-27 00:00:00.000 5
10 445 2011-01-28 00:00:00.000 6
11 445 2011-01-28 00:00:00.000 7
12 445 2011-01-28 00:00:00.000 8
13 445 2011-01-29 00:00:00.000 9
14 6 2011-02-01 00:00:00.000 1
15 8 2011-02-03 00:00:00.000 1
16 1 2011-03-30 00:00:00.000 3
17 1 2011-03-30 00:00:00.000 4
18 1 2011-03-30 00:00:00.000 5
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5848 Visits: 11408
What about grp 1?

Do you want to return entries for grp 1 in records 16, 17 and 18 as well as 1 and 2?

If not - can you specify formal criteria why rows 16, 17, 18 should not be included?
earbash
earbash
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 130
Thankyou for you reply.

grps can be updated many times on many dates, so can appear in the table many times.

The table is ordered by date, so I am only interested in the first 'n' rows plus however many rows needed to get all items for the grp.
In this case, its 8 + 5 rows.

So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8404 Visits: 19508
AllanP999 (3/13/2013)
Thankyou for you reply.

grps can be updated many times on many dates, so can appear in the table many times.

The table is ordered by date, so I am only interested in the first 'n' rows plus however many rows needed to get all items for the grp.
In this case, its 8 + 5 rows.

So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows


Here's one solution:

with Groups
as (
select top 8 grp
from @tbl
order by dte
)
,GroupsDistinct
as (
select distinct grp
from Groups
)
select t.*
from @tbl t
join GroupsDistinct gd on t.grp = gd.grp



This does, however, bring back ids 16, 17 and 18 because they are in grp 1 (which is selected by the Id 1 row). If these rows should not be selected, you need to modify your description of the problem, as already pointed out by Sergiy.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22812
Here's another, same caveats apply

WITH CTE AS 
(
SELECT id,grp,dte,
ROW_NUMBER() OVER(ORDER BY dte ASC, grp) AS rn
FROM @tbl tbl
),
CTE2 AS (
SELECT id,grp,dte,rn,
MIN(rn) OVER(PARTITION BY grp) AS MinRn
FROM CTE)
SELECT id,grp,dte
--,rn,MinRn
FROM CTE2
WHERE MinRn <= 8
ORDER BY rn;



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




earbash
earbash
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 130
Great I now have the solution. Many thanks to you all.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16641 Visits: 17027
AllanP999 (3/14/2013)
Great I now have the solution. Many thanks to you all.


Which one did you use or was it a combination? It would be great if you would share what you used as a solution to help other that may stumble into this thread in the future.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
earbash
earbash
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 130
Phil's solution sorted it out thanks
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5848 Visits: 11408
AllanP999 (3/15/2013)
Phil's solution sorted it out thanks


So, your answer on my question was not correct:
So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows


as Phil's solution returns rows 16 and 17.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search