Paging query help - 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 2000 Forums

SQL Server Development (2000)

Paging query help

Author

Topic

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-10-17 : 03:19:45

I am trying to page some data that isn't really the best, it has some duplicates but I have been told this is the way the system has to be (it imports data from another source). Below is an example of the data:


CREATE TABLE Test (
	[ID] int PRIMARY KEY,
	memberID int,
	title varchar(50)
)
GO
INSERT INTO TEST VALUES (1,1,'A')
GO
INSERT INTO TEST VALUES (2,1,'A')
GO
INSERT INTO TEST VALUES (3,2,'B')
GO
INSERT INTO TEST VALUES (4,3,'C')
GO
INSERT INTO TEST VALUES (5,3,'C')
GO
INSERT INTO TEST VALUES (6,3,'D')
GO

From this I would only like to return records 1,3,4 and 6, but I still need to return all the data so I just can't just select DISTINCT.

I also thought I could just return all the rows, then in my app only show the row if the previous row had different data, but I am paging my data and doing this would cause problems with the number of records I would tell the user I am returning (I am using this paging technique - http://www.aspfaqs.com/webtech/062899-1.shtml).

Any ideas on how I can just return those 4 rows?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 04:25:03

You can create a view instead and do paging of the view data

create view vwTEST as

select * from test

where not exists

(select 0 from test t where t.title=test.title and t.[id]<test.[id])

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 04:51:23

Or (if [id] has no gaps):

select * from test

where not exists

(select 0 from test t where t.title=test.title and t.[id]=test.[id]-1)

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-10-19 : 20:19:00

Thanks Stoad, but I couldn't get this query to work, plus looking at it I don't think it will because there could be 3 records with the same value, so the last row could be INSERT INTO TEST VALUES (6,3,'C'). I'll have a look into creating a view.

benricho
Yak Posting Veteran

84 Posts

Posted - 2003-10-19 : 20:22:52

Actually, I used the first query (with the <) and it worked like a charm, thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources