Kategorial`noe ordering of results of search

Practical questions which arise at job with databases, often demand decisions of non-standard character, forcing to distract for an instant from zapal`cevannogo the monitor and, having taken a pencil, poimprovizirovat` on a paper, concentrating first of all on algorithm of the decision... And only after - on realization. Having (I hope quite good) a habit brauzit` the forums devoted SUBD, encounter on various questions devoted not only to problems of installation a component, syntax SQL, access to a DB... But also the problems{tasks} of algorithmic character representing practical interest. nastojahhjaja clause{article} also is devoted to one of such questions.

So, the formulation in which I met this problem{task} last time before a spelling of clause{article} was approximately those (the original, speaking frankly, was rather indistinct, therefore I shall try in nekoej to a measure to paraphrase, lowering{omitting} excessive details): there is table Images (ImageID INT, CategoryID INT, [Relevance] FLOAT), containing numbers{rooms} of figures (ImageID), some category (CategoryID) to which he belongs, and relevance ([Relevance]) is compared with each figure. The matter is that the problem{task} of the developer included realization certain " a search cursor " which by the certain criteria would return corresponding set of figures. Vobhhim as it was clear from a post, he has constructed a cursor according to all requirements of the customer... Except for one. Result of the search returned by a cursor, it was necessary to order so that have turned out (to not confuse "trains" the request to concept "train" of the theory of relational databases) the following kind. In the first train on one of each category (ordered on CategoryID), the most relevant figures should enter into the second - the following are lower on relevance (that is, no more relevant, than the first, but most relevant of the rest) also on one of each category, etc. Differently, the result of search to a cursor should be the report consisting of columns, in each of which there are the figures belonging to the fixed category, and ordered inside a column agrees relevanitnosti.

I want to note, that in the original relevance did not contain in the table, and was definitely calculated on the basis of values of some sizes taken from other tables. But it does not limit in no way a generality of reasonings... We simply hide superfluous details. Besides categories of figures is present about a dozen (differently, a small number, but not fixed), in turn the developer resulted provisional number of figures - about{near} 200000. Figures are approximately in regular intervals distributed{allocated} on categories, but "approximately" means, that last trains will be incomplete, that is generally at them there will be figures not from all categories. Well and, naturally, the reader has already noticed, that for the formulation of a problem{task} is absolutely unimportant, we consider{examine} what objects (be figures, as in our case, or names podruzhek... Ordered on relevance). Main is a presence of two attributes: grupirujuhhego (CategoryID) and uporjadyvajuhhego ([Relevance]).

Well. We pass to the decision. As razarabotchik used MS SQL Server 2000 also we shall use the same SQL-server. to us also it is required Transact-SQL. But all over again - algorithm. So, we create the time table which except for stolbcov tables Images will contain two more: Ind and Ind2 the whole type. In this table (we shall name her{it} @Tab) we shall place all recordings from Images, ordered on CategoryID (on vozrostaniju) as a primary attribute and on [Relevance] (on decrease) as secondary, having put down them{him;it} sootvetsvujuhhie values of field Ind from 1 up to the number equal to quantity{amount} of recordings in Images. And now - the most important step. More precisely, sequence of steps. We count up, how much figures (recordings) in all categories, except for last (according to ordering on increase). Start up this number @x. Then in each recording last category to field Ind2 we appropriate{give} value Ind - @x. On the following step recordings in all categories are counted up, how much, except for two last and we place value in @x. In each recording in a penultimate category to field Ind2 we appropriate{give} value Ind - @x, etc., yet we shall not fill in value Ind2 in all recordings. Thus we achieve that Ind2 within the framework of each category will change from edenicy up to the number equal to quantity{amount} of figures in a category (it is natural, within the framework of a category the ordering set Ind2, will coincide with ordering on relevance). And, at last, we order now @Tab on Ind2 (on increase) as a primary attribute and on relevance (on decrease) as secondary. The following code illustrates said.

DECLARE @Tab TABLE (ImageID INT, CategoryID INT, [Relevance] FLOAT, Ind INT IDENTITY (1, 1), Ind2 INT)

INSERT INTO @Tab (ImageID, CategoryID, [Relevance])

SELECT * FROM Images

ORDER BY CategoryID ASC, [Relevance] DESC

DECLARE @Cat TABLE (CategoryID INT)

INSERT INTO @Cat

SELECT DISTINCT CategoryID FROM Images

DECLARE @Curr_Cat_Quant INT

SELECT @Curr_Cat_Quant = COUNT (*) FROM @Cat

DECLARE @Var1 INT

DECLARE @Var2 INT

WHILE @Curr_Cat_Quant> 0

BEGIN

        SELECT @Var1 = MAX (CategoryID) FROM @Cat

        SELECT @Var2 = COUNT (*) FROM @Tab A INNER JOIN @Cat B ON A.CategoryID = B.CategoryID

        WHERE A.CategoryID! = @Var1

        UPDATE @Tab

        SET Ind2 = Ind - @Var2

        WHERE CategoryID = @Var1

        DELETE FROM @Cat

        WHERE CategoryID = @Var1

        SELECT @Curr_Cat_Quant = COUNT (*) FROM @Cat

END

The reader already had, probably, question: why so why to not create for search from @Tab the cursor and to not be run on recordings, appropriating{giving} Ind2 at once necessary value... And vobhhe, two fields Ind and Ind2 what for are necessary. But here just all business in productivity. Such code it is appreciable would brake job of system as in each group at us it is a lot of figures, and FETCH - "expensive" operation. Instead of it we suggest the SQL-server to fill itself Ind (by means of avtoinkrementa) and then for some steps (as categories at us a little) to process Ind2 with the help of faster design INSERT... SELECT.

It is necessary to make final search which gives us necessary result:

SELECT * FROM @Tab

ORDER BY Ind2 ASC, [Relevance] DESC

I shall be rather grateful for comments concerning my clause{article}.