Find sequences - 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)

Find sequences

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 14:19:01

Im having trouble with writing a SP to find Sequences.

- The table contains users and their price offers.
- The table contains 1000's of user and price offers.
- The table is sorted by Price column.

I need to find PriceID of rows of users which have 3 or more price offers in a sequence/row.

here is a sample table:

UserID | Price | PriceID
100      95.50     200
100      95.30     201
101      94.20     304
100      93.20     204
107      91.02     221
106      91.01     222
100      90.66     221
100      87.01     195
100      85.02     194
100      80.33     300
100      66.11     987
103      66.10     111
103      66.09     255
104      54.54     544
104      11.23     654
104      10.56     364
104      10.54     777

I'm trying to write a SP that will produce the following result
PriceID
300
987
777

THX!!

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 14:29:45

Here is a query I use to detect duplicates. I modified it for you to detect rows with more than 2 id's.

SELECT Userid,Price,PriceID
FROM SomeTable
WHERE Userid
IN ( SELECT Userid
FROM SomeTable
GROUP BY Userid
HAVING Count(*) >2
)
ORDER BY Userid

OUTPUT:
-------

USERID     PRICE       PRICE ID
100        90.66       221
100        87.01       195
100        85.02       194
100        80.33       300
100        66.11       987
104        54.54       544
104        11.23       654
104        10.56       364
104        10.54       777

The output you posted did not seem in any particular order so you may need to modify my query to get what you want.

hope that helps u !

r&r

r&r

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-20 : 14:41:10

[code]
Select PriceID from
(Select row_number() over(order by UserID)as seq,* from Table)t
where t.seq>=3
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 14:43:44

Are duplicates the same thing as a sequence?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 14:44:36

What defines your order of records in the table?

E 12°55'05.63"
N 56°04'39.26"

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 14:48:52

Hello;

Based on his table it appears as though it is ordered by UserId. I have an unnormalized database which is similar in structure and the query I posted is how I retrieve duplicate records (ie.. parts) which share a similar invoice.

Ordering the results by UserID gives you sequence (so to speak).

I guess he would need to further define sequence if its different..

r&r

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 14:53:41

quote:
Originally posted by revdnrdy

Based on his table it appears as though it is ordered by UserId.

No. UserID 100 returns at line 4.

E 12°55'05.63"
N 56°04'39.26"

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 14:54:55

Thanks !

The output of the priceId doesn't really matter .
I plan on using the output of PriceID's to update a column on those priceID's rows.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 15:04:54

A sequence is:
4 rows or more with same userid with different price , (without any other userid in the middle of the sequence).

one very important thing , the table is based on PriceID , which is a auto generated identity of the prices tables

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 15:30:44

revdnrdy,

quote:
SELECT Userid,Price,PriceID
FROM SomeTable
WHERE Userid
IN ( SELECT Userid
FROM SomeTable
GROUP BY Userid
HAVING Count(*) >4
)
ORDER BY Userid

thanks for the answer but,
this would result in showing any userid with more than 4 rows , no?
it doesn't take into account that those rows have to be in a sequence

A sequence is:
4 rows or more with same userid with different price , (without any other userid in the middle of the sequence , when sorting based on price).

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 15:38:18

thanks , sodeep!

quote:
Select PriceID from
(Select row_number() over(order by UserID)as seq,* from Table)t
where t.seq>=3

this doesn't work also , as i understand this adds row number to the table while ordered by UserID.

than it just skips the first 4 rows and returns the rest of the rows.
no?

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 15:46:22

maybe im not explaining my self good enough :)

currently i have C# code that does this for me , ill explain the logic , maybe it will help you guys figure out what im looking for :)

Here are the steps:

1. sort the table based on Price.
2. loop on the table , take the userid from the first row, keep it in a variable.
3. see if the next row has the same userId , if so increment a counter variable.
4. repeat step 3 until you find a different userid.
5. if counter variable is > 4 , we found a sequence.

hmmm.. hope that help :) thx!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 15:53:37

Sigh...
What defines the order of records in your table?
This is vital for checking a sequence.

E 12°55'05.63"
N 56°04'39.26"

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 16:08:24

table is based on the PriceID , each new price offered by user gets a new PriceID

so from sql point of view records are sorted this way (based on PriceID)

UserID | Price | PriceID
103      66.10     111
100      85.02     194
100      87.01     195
100      95.50     200
100      95.30     201
100      93.20     204
107      91.02     221
106      91.01     222
100      90.66     223
103      66.09     255
100      80.33     300
101      94.20     304
104      10.56     364
104      54.54     544
104      11.23     654
104      10.54     777
100      66.11     987

but when looking for sequence what is important is the Price and UserId
so first step would be sorting table by Price

UserID | Price | PriceID
100      95.50     200
100      95.30     201
101      94.20     304
100      93.20     204
107      91.02     221
106      91.01     222
100      90.66     223
100      87.01     195
100      85.02     194
100      80.33     300
100      66.11     987
103      66.10     111
103      66.09     255
104      54.54     544
104      11.23     654
104      10.56     364
104      10.54     777

now starting from the top of the table and going down...
100 95.50 200
100 95.30 201

this is a sequence of two , im looking for sequence of 3+.
--------------

100 90.66 223
100 87.01 195
100 85.02 194
100 80.33 300
100 66.11 987

this is a sequence of five , (should be included in the output)

------

104 54.54 544
104 11.23 654
104 10.56 364
104 10.54 777

this is a sequence of four , (should be included in the output)

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-20 : 16:09:24

By the way if you are doing this in C# why are you looking for an sql query? Just loop thru your resultset in the C# code.

r&r

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 16:13:15

I run this function every few seconds and i have high pressure on the website.
It requires me fetching data from the db and running two nested for loops to find the sequences.

I figured out SQL would be much faster and efficient , wouldn't it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 16:18:47

This should help

DECLARE	@Sample TABLE
	(
		UserID INT,
		Price MONEY,
		PriceID INT
	)

INSERT	@Sample
SELECT	100, 95.50, 200 UNION ALL
SELECT	100, 95.30, 201 UNION ALL
SELECT	101, 94.20, 304 UNION ALL
SELECT	100, 93.20, 204 UNION ALL
SELECT	107, 91.02, 221 UNION ALL
SELECT	106, 91.01, 222 UNION ALL
SELECT	100, 90.66, 221 UNION ALL
SELECT	100, 87.01, 195 UNION ALL
SELECT	100, 85.02, 194 UNION ALL
SELECT	100, 80.33, 300 UNION ALL
SELECT	100, 66.11, 987 UNION ALL
SELECT	103, 66.10, 111 UNION ALL
SELECT	103, 66.09, 255 UNION ALL
SELECT	104, 54.54, 544 UNION ALL
SELECT	104, 11.23, 654 UNION ALL
SELECT	104, 10.56, 364 UNION ALL
SELECT	104, 10.54, 777

SELECT		s.PriceID
FROM		@Sample AS s
CROSS APPLY	(
			SELECT TOP 4	x.UserID
			FROM		@Sample AS x
			WHERE		x.Price >= s.Price
			ORDER BY	x.Price
		) AS f
GROUP BY	s.PriceID
HAVING		COUNT(DISTINCT f.UserID) = 1
		AND COUNT(f.UserID) = 4

E 12°55'05.63"
N 56°04'39.26"

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-20 : 16:24:27

quote:

Originally posted by Peso

This should help

DECLARE	@Sample TABLE
	(
		UserID INT,
		Price MONEY,
		PriceID INT
	)

INSERT	@Sample
SELECT	100, 95.50, 200 UNION ALL
SELECT	100, 95.30, 201 UNION ALL
SELECT	101, 94.20, 304 UNION ALL
SELECT	100, 93.20, 204 UNION ALL
SELECT	107, 91.02, 221 UNION ALL
SELECT	106, 91.01, 222 UNION ALL
SELECT	100, 90.66, 221 UNION ALL
SELECT	100, 87.01, 195 UNION ALL
SELECT	100, 85.02, 194 UNION ALL
SELECT	100, 80.33, 300 UNION ALL
SELECT	100, 66.11, 987 UNION ALL
SELECT	103, 66.10, 111 UNION ALL
SELECT	103, 66.09, 255 UNION ALL
SELECT	104, 54.54, 544 UNION ALL
SELECT	104, 11.23, 654 UNION ALL
SELECT	104, 10.56, 364 UNION ALL
SELECT	104, 10.54, 777

SELECT		s.PriceID
FROM		@Sample AS s
CROSS APPLY	(
			SELECT TOP 4	x.UserID
			FROM		@Sample AS x
			WHERE		x.Price >= s.Price
			ORDER BY	x.Price
		) AS f
GROUP BY	s.PriceID
HAVING		COUNT(DISTINCT f.UserID) = 1
		AND COUNT(f.UserID) >= 3

E 12°55'05.63"
N 56°04'39.26"

Well done Peso. Fixed to satisfy original requirement.

shaharru
Yak Posting Veteran

72 Posts

Posted - 2009-01-20 : 16:37:01

THX!!

This seems to work perfectly!

i guess that the "SELECT TOP 4 x.UserID" should also be changed to "SELECT TOP 3 x.UserID"

THE only problem is i dont really understand it :(
Im going to try and figure it out :)

thank you peso and Skorch

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 16:39:03

Thank you. Changing the COUNT clause to >=3 will yield more records in the final resultset based in first post from OP.

I hope OP still follow this topic.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 16:42:01

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
UserID INT,
Price MONEY,
PriceID INT
)

INSERT @Sample
SELECT 100, 95.50, 200 UNION ALL
SELECT 100, 95.30, 201 UNION ALL
SELECT 101, 94.20, 304 UNION ALL
SELECT 100, 93.20, 204 UNION ALL
SELECT 107, 91.02, 221 UNION ALL
SELECT 106, 91.01, 222 UNION ALL
SELECT 100, 90.66, 221 UNION ALL
SELECT 100, 87.01, 195 UNION ALL
SELECT 100, 85.02, 194 UNION ALL
SELECT 100, 80.33, 300 UNION ALL
SELECT 100, 66.11, 987 UNION ALL
SELECT 103, 66.10, 111 UNION ALL
SELECT 103, 66.09, 255 UNION ALL
SELECT 104, 54.54, 544 UNION ALL
SELECT 104, 11.23, 654 UNION ALL
SELECT 104, 10.56, 364 UNION ALL
SELECT 104, 10.54, 777

-- Initialize user supplied parameter
DECLARE @MinSequence INT

SET @MinSequence = 4

-- Display the final resultset
SELECT s.PriceID
FROM @Sample AS s
CROSS APPLY (
SELECT TOP (@MinSequence)
x.UserID
FROM @Sample AS x
WHERE x.Price >= s.Price
ORDER BY x.Price
) AS f
GROUP BY s.PriceID
HAVING COUNT(DISTINCT f.UserID) = 1
AND COUNT(f.UserID) = @MinSequence[/code]Yes, Skorch is a rock!

E 12°55'05.63"
N 56°04'39.26"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources