Ranking Query: Choosing values "between" Ranks -- sql server programming

sql server programming : Ranking Query: Choosing values "between" Ranks

Trey Walpole
4/20/2004 5:15:56 PM

Where are you storing these queries if you're not using stored procedures?
Or is that you don't have rights to create sp's?

for #1 and 2
Since it's not in an sp, I imagine you are building the sql dynamically in
the app before passing it in - in that case, these can be done there.
[and I assume you mean ranked by any column, since you'll typically want to
order by the rank...]
Make sure each column that would potentially be ranked has an index.

for #4 (using population as an example):

select * from
(
SELECT T1.CountryName, T1.Population, T1.Hemisphere,
(SELECT Count(T2.CountryName)
FROM Country as T2
WHERE T2.Population <= T1.Population) as r
FROM Country as T1
) rankedSet (CountryName, Population, Hemisphere, r)
where r>=3 and r<=8
order by r

[quoted text, click to view]

Anith Sen
4/20/2004 5:38:33 PM

You can wrap your existing query in a derived table & do:

SELECT *
FROM (
SELECT T1.CountryName, T1.Population, T1.Hemisphere,
( SELECT COUNT(T2.CountryName) + 1
FROM Country AS T2
WHERE T2.CountryName < T1.CountryName )
FROM Country T1
) D ( CountryName, Population, Hemisphere, rank )
WHERE rank BETWEEN 3 AND 8
ORDER BY rank;

You can also use the subquery in your WHERE clause like:

SELECT T1.CountryName, T1.Population, T1.Hemisphere
FROM Country T1
WHERE ( SELECT COUNT(T2.CountryName) + 1
FROM Country AS T2
WHERE T2.CountryName < T1.CountryName ) BETWEEN 3 AND 8 ;

--
Anith

Anith Sen
4/20/2004 8:50:21 PM

Well, when you add a filter in the outer query, you need to have the same
filter in your sub-query or have it as a correlation. So the correct SQL
statement would be:

SELECT *
FROM ( SELECT T1.CountryName, T1.Population, T1.Hemisphere,
( SELECT COUNT( * ) + 1
FROM Country AS T2
WHERE T2.Hemisphere = T1.Hemisphere
AND T2.CountryName < T1.CountryName )
FROM Country T1
WHERE T1.Hemisphere = 'N'
) D ( CountryName, Population, Hemisphere, rank )
WHERE rank BETWEEN 3 AND 8
ORDER BY rank;

--
Anith

Abdullah Kauchali
4/20/2004 11:50:21 PM

(Script at the end of this message).

How do I do the following:

1. Select the required table rows based on a criteria (WHERE clause)
2. Order by *any* column
3. Rank the above results beginning with 1 to ... whatever
4. Select ONLY the values between 3 and 8 of the Rank (including).

Provisos:
1. No stored procs;
2. No creation of new tables

So far, I have managed to struggle to this query:

SELECT T1.CountryName, T1.Population, T1.Hemisphere,
(SELECT Count(T2.CountryName) + 1
FROM Country as T2
WHERE T2.CountryName < T1.CountryName) as r
FROM Country as T1
ORDER BY r

which yields this:

CountryName Population Hem.. r
----------- ---------- --- --
ANGOLA 6495000 S 1
BRAZIL 480000000 S 2
CANADA 387474 N 3
EGYPT 34000 N 4
GERMANY 4300500 N 5
PERU 48857 S 6
SPAIN 474400 N 7
UGANDA 498575 N 8
UK 764000 N 9
USA 40000000 N 10

Any help from here?

Many thanks and kind regards,

Abdullah

<script>
CREATE TABLE Country (
CountryName varchar(50) NOT NULL,
Population int NULL,
Hemisphere varchar(1) NULL
)
go

ALTER TABLE Country
ADD PRIMARY KEY (CountryName)
go

INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('USA',
'40000000', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('CANADA',
'387474', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('BRAZIL',
'480000000', 'S');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('PERU',
'48857', 'S');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('UGANDA',
'498575', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('UK',
'764000', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('EGYPT',
'34000', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('GERMANY',
'4300500', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('SPAIN',
'474400', 'N');
INSERT INTO Country (CountryName, Population, Hemisphere) VALUES ('ANGOLA',
'6495000', 'S');
</script>

Abdullah Kauchali
4/21/2004 1:27:02 AM

[quoted text, click to view]

Hi Anith,

Many thanks for the reply.

If I append a where clause, the ranking still happens with the original
non-filtered list. The sequencing gets broken.

So:

SELECT *
FROM (
SELECT T1.CountryName, T1.Population, T1.Hemisphere,
( SELECT COUNT(T2.CountryName) + 1
FROM Country AS T2
WHERE T2.CountryName < T1.CountryName )
FROM Country T1 Where T1.Hemisphere = 'N'
) D ( CountryName, Population, Hemisphere, rank )
WHERE rank BETWEEN 3 AND 8
ORDER BY rank;

results in:

CANADA 387474 N 3
EGYPT 34000 N 4
GERMANY 4300500 N 5
SPAIN 474400 N 7
UGANDA 498575 N 8

what I am hoping of getting is:

GERMANY 4300500 N 3
SPAIN 474400 N 4
UGANDA 498575 N 5
UK 764000 N 6
USA 40000000 N 7

Abdullah

Abdullah Kauchali
4/21/2004 1:34:32 AM

[quoted text, click to view]

Hi Trey,

The queries have always been in SQL - in the code. We've never used sp's ...
after googling for this, I found some answers with sp's - which we can't
use.

[quoted text, click to view]

That's fine.

[quoted text, click to view]

Is it possible to do a complete query (with where clauses e.g. where
Hemisphere = 'N') and *then* do the ranking, and thereafter the "where r>=3
and r<=8")?

Many thanks

Abdullah

Abdullah Kauchali
4/21/2004 8:43:08 AM

[quoted text, click to view]

Anith,

Thanks for that! What help!!! :)

As long as the "where" conditions are the same for both queries, it works
like a charm - as you explained. It seems, though, that I can only order by
one column at a time by using the correct column in "AND T2.[ColumnName] <
T1.[ColumnName"). This is not so much a problem - would have been nice to
have to allow "order by column1, column 2.. etc." before doing the ranking.

Anyway, what do you think about using this query for substituting ADO
Recordset paging?

Regards

Abdullah

Anith Sen
4/21/2004 8:51:21 AM

[quoted text, click to view]
the correct column <<

Actually, you can do the ranking on as many columns as you need. Please post
a repro with DDLs & sample data which requires such multiple columns in the
sub-query. For example, the subquery's WHERE clause for ranks involving more
than one column, would be along the lines of:

WHERE t1.col1 <= t2.col1
OR (t1.col1 = t2.col1 AND t1.col2 <= t2.col2)
OR (t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.col3 <= t2.col3)
OR ...

[quoted text, click to view]
paging? <<

Depending on your requirements, you can use this method. But make sure to
test it thoroughly for performance, since complex comparison routines on
larger datasets can slow things up. Here is another link which shows
different approaches for paging: http://www.aspfaq.com/show.asp?id=2120

--
Anith

Abdullah Kauchali
4/23/2004 8:40:16 AM

[quoted text, click to view]

(Apol for delayed response.)

[quoted text, click to view]

Again correct. I just tested this query on a moderately large table and the
results were disappointing. It seems, the count(*) + 1 simulates
"row-by-row" operation and SQL Server starts thrashing.

[quoted text, click to view]

Great link. Got this query from there:

SELECT TOP 10 CountryName, Population, Hemisphere
FROM (SELECT TOP 30 CountryName, Population, Hemisphere
FROM Country
-- put-where-clause-here: WHERE Country LIKE '%'+'U'+'%'
ORDER BY CountryName Desc) A -- put order by here
ORDER BY CountryName Desc -- must match above

This one is infinitely more efficient than the previous one.

Also, the syntax for ORDER BY is now simplified.

Many thanks and kind regards

Abdullah

Abdullah Kauchali
4/23/2004 9:51:54 AM

[quoted text, click to view]

(Apol for delayed response.)

[quoted text, click to view]

Again correct. I just tested this query on a moderately large table and the
results were disappointing for using it as a substitute for ADO Recordset
paging. It seems, the count(*) + 1 simulates a "row-by-row" operation and
SQL Server starts thrashing.

[quoted text, click to view]

Great link. Got this query from there:

SELECT CountryName, Population, Hemisphere FROM
(SELECT TOP 2 CountryName, Population, Hemisphere
FROM (SELECT TOP 2 CountryName, Population, Hemisphere
FROM Country
WHERE Population > '34000' AND
Hemisphere like '%N%'
ORDER BY Population Asc) A -- MUST BE OPPOSITE OF B
ORDER BY Population Desc) B -- MUST BE OPPOSITE OF A
ORDER BY Population Asc -- ORDER BY NOW SIMPLER

This one is infinitely more efficient than the previous one.

The only "tricky" part is to determine the final page:

if ((resultset<pagesize) or
(checkForDuplicates(previousResultSet)) then eof

where checkForDuplicates will return a true even if ONE record from the
previous resultset is contained in the current resultset.

Is there a simpler way to determine eof?

Many thanks and kind regards

Abdullah

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008

home · blog · groups

Questions? Comments? Contact the dn