May 19, 2007 at 12:46 pm
1) How can I select only the first record in a series of certain consecutive numbers?
2) How can I obtain the count for the number of times a set of those certain consecutive numbers appear?
Example: (this assumes an un-altered Northwind database)
USE Northwind
GO
WITH Peter AS
(SELECT EmployeeID, Freight,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Freight) AS [RecordNumber]
FROM Orders)
SELECT * FROM Peter
Partial Result Set
EmployeeID Freight RecordNumber
-------------- -------- ------------------
1 0.21 1
1 0.45 2
1 0.93 3
1 1.27 4
1 1.35 5
1 1.36 6
1 1.51 7
1 1.66 8
1 2.50 9
1 3.94 10
1 4.03 11
1 4.27 12
1 4.41 13
1 4.98 14
1 4.99 15
1 4.99 16
1 7.46 17
From the list, records 5 and 6 show consecutive Freight figures of 1.35 and 1.36 for EmployeeID 1.
From the list, records 14 and 15 show consecutive Freight figures of 4.98 and 4.99 for EmployeeID 1. (Ignore duplicate Freight figures for this question)
In this example how could I get a result set that only showed records, 1.35 and 4.98 - that is the first record in a series of consecutive Freight figures?
How could I get a count of the number of consecutive records for each set?
A partial result set should look like this.
EmployeeID 1stFreightRecord Count
1 1.35 2
1 4.98 2
May 20, 2007 at 6:39 am
I have already posted the result in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=367397
Maybe it is better not to post the same question in 2 threads.
Jan
May 20, 2007 at 10:54 am
Jan,
Thanks again for trying. However, your "result" does match the result set sought after in my post as shown in the last few lines. Your results lack the count. In addition, the request states to ignore duplicates. Your result shows a duplicate.
Maybe it is better to post the same question in 3 threads.
May 20, 2007 at 12:15 pm
I replied on:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=367397
I think most users would agree that it is annoying to post the same question in several threads. This is not helpful for other people who are reading this and who can possibly evaluate and correct my tries. We are trying to help you.
May 20, 2007 at 12:24 pm
I would agree that it would be annoying if I posted this question twice in this forum, but I didn't. This site has two forums for T-SQL. One for SQL 2005 and one for SQL 2000/7 (Perhaps you find that annoying). The question was posted in the same manner - once in each forum for T-SQL. My posting of the question is consistent with the site's design.
May 24, 2007 at 6:26 pm
SOLUTION
SELECT
EmployeeID
,LowFreight
,(
SELECT count(*) FROM Orders PS
WHERE PS.EmployeeID = T.EmployeeID AND (PS.Freight BETWEEN T.LowFreight AND T.HighFreight)
) as [SeriesRowCount]
FROM
(
SELECT EmployeeID, Freight as LowFreight
,(
SELECT min(Freight) FROM Orders PS3
WHERE EXISTS
(
SELECT * FROM Orders PS2
WHERE ( PS2.EmployeeID = PS3.EmployeeID AND PS2.Freight = (PS3.Freight - .01) )
)
AND NOT EXISTS
(
SELECT * FROM Orders PS2
WHERE ( PS2.EmployeeID = PS3.EmployeeID AND PS2.Freight = (PS3.Freight + .01) )
)
AND PS3.EmployeeID = PS1.EmployeeID
AND PS3.Freight > PS1.Freight
) as HighFreight
FROM Orders PS1
WHERE EXISTS
(
SELECT * FROM Orders PS2
WHERE ( PS2.EmployeeID = PS1.EmployeeID AND PS2.Freight = (PS1.Freight + .01) )
)
AND NOT EXISTS
(
SELECT * FROM Orders PS2
WHERE ( PS2.EmployeeID = PS1.EmployeeID AND PS2.Freight = (PS1.Freight - .01) )
)
) T
ORDER BY EmployeeID, LowFreight
May 25, 2007 at 1:28 am
Are you using SQL 2000 or SQL 2005? If you are using 2000, post in the 2000 forums. If you are using 2005, please post in the 2005 forums.
Most of the people who answer the questions use the active threads, so we see all active discussins.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 25, 2007 at 1:32 am
both
May 27, 2007 at 10:32 pm
cross posting is a no-no.
it may not be expressly forbidden by the site's rules, but there's no denying that it is bad form. ask any regular here or any other similar site and you'll get a similar opinion.
it wastes the time of people that are trying to help you, since someone could spend time coming up with an answer when it's already been answered elsewhere.
---------------------------------------
elsasoft.org
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply