Select grouping and sorting. - 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

Transact-SQL (2000)

Select grouping and sorting.

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2008-12-01 : 10:29:46

I am doing a website for a group of real estate agents. When showing the first search page we display the 10st properties on page 2 properties 11th to 20, etc. All the paging is beeing handled by the application.
The trick is that my user wants that properties appear one per agent sorted randomly. I know how to do the random part but I can't figure out the select to alternate one property by agent.


DECLARE @TT table
( Pr_Id int,
 Pr_RealEstateAgent_Id int
)

INSERT INTO @TT VALUES ( 1, 1)
INSERT INTO @TT VALUES ( 2, 2)
INSERT INTO @TT VALUES ( 3, 1)
INSERT INTO @TT VALUES ( 4, 3)
INSERT INTO @TT VALUES ( 5, 1)
INSERT INTO @TT VALUES ( 6, 1)
INSERT INTO @TT VALUES ( 7, 1)
INSERT INTO @TT VALUES ( 8, 2)
INSERT INTO @TT VALUES ( 9, 2)
INSERT INTO @TT VALUES ( 10, 4)
INSERT INTO @TT VALUES ( 11, 5)
INSERT INTO @TT VALUES ( 12, 3)
INSERT INTO @TT VALUES ( 13, 5)
INSERT INTO @TT VALUES ( 14, 3)
INSERT INTO @TT VALUES ( 15, 3)

Select Pr_RealEstateAgent_Id,Pr_Id FROM @TT

Expected result (without randomizing the agents)

jean-luc
www.corobori.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 12:26:34

[code]ORDER BY ROW_NUMBER() OVER (PARTITION BY Pr_RealEstateAgent_Id ORDER BY Pr_Id), Pr_RealEstateAgent_Id][/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 12:41:53

quote:
Originally posted by Peso

ORDER BY ROW_NUMBER() OVER (PARTITION BY Pr_RealEstateAgent_Id ORDER BY Pr_Id), Pr_RealEstateAgent_Id]

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

not sure if OP can use this as he has asked in 2000 forum

Corobori
Posting Yak Master

105 Posts

Posted - 2008-12-01 : 13:13:29

Using SQL Server 2000 therefore ROW_NUMBER is not an option.

jean-luc
www.corobori.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-01 : 13:15:18

Then it's getting ugly...

FROM @TT  AS t
ORDER BY (select count(*) From @tt as y where y.Pr_RealEstateAgent_Id = t.Pr_RealEstateAgent_Id and y.pr_id < t.pr_id),
t.Pr_RealEstateAgent_Id

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources