Getting lost in Group, Top for Select

Question

Getting lost in Group, Top for Select

Corobori

Ten Centuries

Points: 1316
More actions
December 1, 2008 at 9:22 am

#73118

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)
11
28
34
410
511
13
29
312
513
15
22
314
16
315
17
jean-luc
http://www.corobori.com

Jean-Luc
www.corobori.com

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

Thanks for the table desc, but I didn't quite understand what you meant with your question. I guess Pr_RealEstateAgent_Id is the agent, and Pr_Id is the property? How is the output ordered then? To me it seems that it's ordered by a another column that isn't in this table.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi Corobori

This will give you yoyur expected results without randomisation - but I think you should find that part relatively straightforward:

WITH MyCTE AS (SELECT ROW_NUMBER() OVER (PARTITION BY Pr_RealEstateAgent_Id ORDER BY Pr_RealEstateAgent_Id) AS SortBy, * from @tt)

SELECT Pr_RealEstateAgent_Id, Pr_Id FROM MyCTE ORDER BY SortBy, Pr_RealEstateAgent_Id, Pr_Id

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Corobori Ten Centuries Points: 1316 More actions · Answer 3

Chris Morris (12/1/2008)
Hi Corobori
This will give you yoyur expected results
ChrisM

I think that ROW_NUMBER is a SQL Server 2005 and in my case I am using a SQL Server 2000

r.hensbergen (12/1/2008)
Thanks for the table desc, but I didn't quite understand what you meant with your question. I guess Pr_RealEstateAgent_Id is the agent, and Pr_Id is the property? How is the output ordered then? To me it seems that it's ordered by a another column that isn't in this table.

The output should show for each Agent one property and so on.

So the output says the following

Agent 1Prop Id #1

Agent 2Prop Id #8

Agent 3Prop Id #4

Agent 4Prop Id #10

Agent 5Prop Id #11

Agent 1Prop Id #3

Agent 2Prop Id #9

Agent 3Prop Id #12

Agent 5Prop Id #13

Agent 1Prop Id #5

Agent 2Prop Id #2

Agent 3Prop Id #14

Agent 1Prop Id #6

Agent 3Prop Id #15

Agent 1Prop Id #7

Jean-Luc
www.corobori.com

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 4

This is how you do ROW_NUMBER() OVER in SQL Server 2000 with any performance at all. It's almost as fast as ROW_NUMBER () OVER...

Using the test data previously provided...

--===== Put the data and some additional randomizing data where we can work on it

SELECT CAST(NULL AS INT) AS SortBy,

NEWID() AS Random,

ISNULL(Pr_RealEstateAgent_Id,0) AS Pr_RealEstateAgent_Id,

ISNULL(Pr_Id,0) AS Pr_Id

INTO #MyHead

FROM @tt

--===== Add the necessary clustered index for the "quirky" update

CREATE CLUSTERED INDEX IXU_#MyHead ON #MyHead (Pr_RealEstateAgent_Id,Random)

--===== Create a couple of variables to use in the "quirky" update and preset them

DECLARE @PrevSortBy INT,

@PrevPr_RealEstateAgent_Id INT

SELECT @PrevSortBy = 0,

@PrevPr_RealEstateAgent_Id = 0

--===== Do the "quirky" update. Think of it as "ROW_NUMBER() OVER" for SQL Server 2000

UPDATE #MyHead

SET @PrevSortBy = SortBy = CASE WHEN @PrevPr_RealEstateAgent_Id = Pr_RealEstateAgent_Id

THEN @PrevSortBy + 1

ELSE 1

END,

@PrevPr_RealEstateAgent_Id = Pr_RealEstateAgent_Id

FROM #MyHead WITH(INDEX(0))

--===== Produce the "semi random" output.

SELECT Pr_RealEstateAgent_Id, Pr_Id

FROM #MyHead

ORDER BY SortBy,Pr_RealEstateAgent_Id --Semi random as requested

--ORDER BY Random --Fully random output

drop table #myhead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 5

For more infomation on the method used above, please see the following link...

[font="Arial Black"]Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5

[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Corobori (12/1/2008)
Chris Morris (12/1/2008)
Hi Corobori
This will give you your expected results
ChrisM
I think that ROW_NUMBER is a SQL Server 2005 and in my case I am using a SQL Server 2000

Apologies, Corobori. Insufficient coffee. Is this problem now solved?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Corobori Ten Centuries Points: 1316 More actions · Answer 7

It looks I got something from another forum

Select 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

Jean-Luc
www.corobori.com

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 8

That'll work... just be advised that it does contain a "partitioned" triangular join and if there're a large number of Pr_ID's for each agent, there will be a big time performance problem. It works fine for small "partitions". Click the following link to find out why...

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Corobori Ten Centuries Points: 1316 More actions · Answer 9

Jeff,

Pretty impressive stuff. I made a change to your code as I had a problem with your solution in the order by. Keep in mind that the idea of this whole query is to display one property for each real estate agent but in random order of the agent not the property, the aim of the whole thing is to make everybody happy by not showing everytime the same agent 1st.

Below is my new version where I added the Agent's name to use in the final SQL.

DECLARE @tt table

( Pr_Id int,

Pr_RealEstateAgent_Id int,

Ag_Name nvarchar(40)

)

INSERT INTO @tt VALUES ( 1, 1, 'Agent 1')

INSERT INTO @tt VALUES ( 2, 2, '2nd Agent')

INSERT INTO @tt VALUES ( 3, 1, 'Agent 1')

INSERT INTO @tt VALUES ( 4, 3, 'A 3rd Agent')

INSERT INTO @tt VALUES ( 5, 1, 'Agent 1')

INSERT INTO @tt VALUES ( 6, 1, 'Agent 1')

INSERT INTO @tt VALUES ( 7, 1, 'Agent 1')

INSERT INTO @tt VALUES ( 8, 2, '2nd Agent')

INSERT INTO @tt VALUES ( 9, 2, '2nd Agent')

INSERT INTO @tt VALUES ( 10, 4, 'Here is the 4 th')

INSERT INTO @tt VALUES ( 11, 5, 'And agent 5th')

INSERT INTO @tt VALUES ( 12, 3, 'A 3rd Agent')

INSERT INTO @tt VALUES ( 13, 5, 'And agent 5th')

INSERT INTO @tt VALUES ( 14, 3, 'A 3rd Agent')

INSERT INTO @tt VALUES ( 15, 3, 'A 3rd Agent')

--===== Put the data and some additional randomizing data where we can work on it

SELECT CAST(NULL AS INT) AS SortBy,

NEWID() AS Random,

ISNULL(Pr_RealEstateAgent_Id,0) AS Pr_RealEstateAgent_Id,

ISNULL(Pr_Id,0) AS Pr_Id,

ISNULL(Ag_Name,0) AS Ag_Name

INTO #MyHead

FROM @tt

--===== Add the necessary clustered index for the "quirky" update

CREATE CLUSTERED INDEX IXU_#MyHead ON #MyHead (Pr_RealEstateAgent_Id,Random)

--===== Create a couple of variables to use in the "quirky" update and preset them

DECLARE @PrevSortBy INT,

@PrevPr_RealEstateAgent_Id INT

SELECT @PrevSortBy = 0,

@PrevPr_RealEstateAgent_Id = 0

--===== Do the "quirky" update. Think of it as "ROW_NUMBER() OVER" for SQL Server 2000

UPDATE #MyHead

SET @PrevSortBy = SortBy = CASE WHEN @PrevPr_RealEstateAgent_Id = Pr_RealEstateAgent_Id

THEN @PrevSortBy + 1

ELSE 1

END,

@PrevPr_RealEstateAgent_Id = Pr_RealEstateAgent_Id

FROM #MyHead WITH(INDEX(0))

--===== Produce the "semi random" output.

SELECT Pr_RealEstateAgent_Id, Pr_Id

FROM #MyHead

ORDER BY SortBy,case when left(Ag_Name, 1) >= CHAR(65 + 26 * rand()) then 0 else 1 end

--ORDER BY Random --Fully random output

drop table #myhead

Jean-Luc
www.corobori.com