July 21, 2006 at 7:49 am
I have a query that needs to return something like:
Rank In this Record Set / Record Count
as one of the fields
The problem is that without putting the data into a table variable and doing an update later or doing a select count (involving some expensive joins) I obviously don't know what the record count is.
I then considered that something along the lines of
RANK() OVER (ORDER BY IndicatorValue DESC) + RANK() OVER (ORDER BY IndicatorValue ASC)
should do it. Though I may need to alter it slightly to avoid ties.
But is there any SQL 2005 function that I'm missing that would give me this functionality with only 1 pass through the data?
July 21, 2006 at 8:23 pm
OK:
I have a working model using something like
((ROW_NUMBER() OVER (ORDER BY UniqueKeyCombination ASC)) + (ROW_NUMBER() OVER (ORDER BY UniqueKeyCombination DESC) )) - 1)
But clearly behind the scenes it is doing far too much work and must actually have the row count to work out one of the above.
Does SQL expose this information to us in any way?
July 25, 2006 at 10:38 am
Here is an article that discusses how to eliminate a cursor. You could follow this model to get your recordcount.
http://www.sqlservercentral.com/columnists/kAli/eliminatingcursors.asp
There always seems to be another way.
Rich
July 25, 2006 at 10:43 am
I'm not using a cursor!
I'm trying to get a sort of PercentRank functionality (though not exactly that)
in one select statement in SQL2005
July 25, 2006 at 10:55 am
My point with my last post was not about using a cursor, it was about the methadology used in the second half of the referenced article. by following the logic laid out here, you can calculate where you are in teh recordset during the WHILE...LOOP. That should allow you to determing the PERCENT_RANK or whatever you need from the entire recordcount.
--now populate this table with the required item category values
INSERT INTO @item_table
SELECT -- Same SELECT statement as that for the CURSOR
it.item_category_id
,ord.order_id
FROM dbo.item_categories it
INNER JOIN dbo.orders ord
ON ord.item_category_id = it.item_category_id
WHERE ord.order_date >= '1-sep-05'
and it.isSuspended != 1
DECLARE @item_category_counter INT
DECLARE @loop_counter INT
SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0) -- Set the @loop_counter to the total number of rows in the
-- memory table
SET @item_category_counter = 1
WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
BEGIN
SELECT @item_category_id = item_category_id
,@order_id = order_id
FROM @item_table
WHERE primary_key = @item_category_counter
--Now pass the item-category_id and order_id to the OUTPUT stored procedure
EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
/*
Call other code here to process your pruchase order for this item
*/
SET @item_category_counter = @item_category_counter + 1
END
Rich
July 26, 2006 at 5:00 am
Thanks for your responses however I'm not using a loop either!
I am returning a ranked set of values and need to calculate the value of (Rank - 1)/RecordCount.
I have achieved this straight from the SELECT with the TSQL I posted above but I realise that this way of getting the RecordCount must cause the database engine unnecessary work.
Therefore I was asking if there was a way I could just access the Record Count (as the database engine must know this to perform it's ranking calculations)
August 10, 2006 at 1:44 pm
Use the "INTO #tmpTable" statement like:
DECLARE @RecordSetCount int,
@StartRow int,
@MaxRows int,
@OrderBy varchar(50) = 'Column1'
SELECT
ROW_NUMBER() OVER (ORDER BY @OrderBy) AS RowNumber,
Column1,
Column2
--notice INTO HERE!
INTO #tmpTable
FROM myTabels
WHERE Stuff = Stuff;
With a stored proc, you can return multiple recordsets, with the first recordset returning your total count and the second returning the results:
recordset #1 with our total record count
SELECT COUNT(*) FROM #tmpTable
recordset #2 with our resultset
SELECT * from #tmpTable WHERE (RowNumber >= @StartRow AND RowNumber < (@StartRow + @MaxRows));
OR
you could set it to our @RecordSetCount variable and add it directly to the resultset like:
SET @RecordSetCount = (SELECT COUNT(*) FROM #tmpTable);
SELECT @RecordSetCount AS MyTotalRecords, * from #tmpTable WHERE (RowNumber >= @StartRow AND RowNumber < (@StartRow + @MaxRows));
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply