SELECT until

#31

In article <hkp7af$6ni$03$1 (AT) news (DOT) t-online.com>, =?ISO-8859-1?Q?Johannes_Ke=DFler?= <mail (AT) bananas-playground (DOT) net> wrote:

Quote:

But if you have a table with 10000 or even more rows, would it not be better to
generate it with the given ORDER BY until the specified customerID is found?
Since all other rows are useless after this row.

NO!!!

It would be better to select ONLY the rows with the customerID you're
interested in!

#32

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 16:09, Doug Miller wrote:

Quote:

In article <hkp7af$6ni$03$1 (AT) news (DOT) t-online.com>, =?ISO-8859-1?Q?Johannes_Ke=DFler?= <mail (AT) bananas-playground (DOT) net> wrote:

But if you have a table with 10000 or even more rows, would it not be better to
generate it with the given ORDER BY until the specified customerID is found?
Since all other rows are useless after this row.

NO!!!

It would be better to select ONLY the rows with the customerID you're
interested in!

but then I do not have the Information I wanted.

but thx anywa,
nemaC gave an answer which worked nicely.

regards,
johannes keÃŸler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwKzcACgkQE++2Zdc7EtctlACbBeLoIppEli Ehla2MqmAg31aH
o1EAnjFQXre40NJiC4AaNJwYn9rmP/AF
=SGOE
-----END PGP SIGNATURE-----

#33

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 16:03, Jerry Stuckle wrote:

Quote:

Doug Miller wrote:
In article <hkp2tr$rc1$03$2 (AT) news (DOT) t-online.com>,
=?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail (AT) bananas-playground (DOT) net> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 13:51, Doug Miller wrote:
In article <hkov40$tnc$00$1 (AT) news (DOT) t-online.com>,
=?ISO-8859-1?Q?Johannes_Ke=DFler?= <mail (AT) bananas-playground (DOT) net> wrote:
Sorry if this is not as clear as it should be.

here is an example

customerid|name|price|points|sell
- ---------------------------------
1|name1|10|22|1
2|name2|3|1|25
3|name3|15|84|23
4|name4|122|81|22

SELECT * FROM table ORDER BY price DESC

result would be:

customerid|name|price|points|sell
- ---------------------------------
4|name4|122|81|22
3|name3|15|84|23
1|name1|10|22|1
2|name2|3|1|25

Now I want to know where in the list or at what possition a specific
customer
is. If I sort by price, customer 3 is at place 2.
If I sort by points it would be at place 1.

But right now there is no such information at which possition the
customer
is,
I
got this only by numbering the rows manually as described as my
previous
reply.
Now the problem is that everytime I want to get a possiton I need
to query
the
whole table with a different ORDER BY. And then number the rows
manually.
If the table has 10000 rows you have to number 10000 rows. And you
have the
data
from 10000 rows.
Image if the customer is at possition 3 you could only number until
you
reach
this specific customer AND do not have to number all the 10000
rows, just to
find out that the customer is a row 3. numbering all the other 9997
rows is
useless and a wast of data which is pulled from the table.

So my question is if I can create a query which just does this without
getting
all the data.

I hope it is more clear now. But english is not my primary language.
Yes, *that* much is clear. What is still unclear is why you think it
is important to know what position that customer is in the table.
see my reply to Captain Paralytic

Oh, that would be the reply in which you fail -- for the fifth time,
at least -- to answer the question "why do you need this information?"

I'm going to repeat -- ONCE -- the advice I gave you in my first
response, with some elaboration: You almost assuredly do not need to
know this. That you think you do is strongly sugggestive of a flawed
design, and I recommend that you re-examine the entire concept. There
is almost certainly a simpler method of accomplishing your purpose,
whatever it is -- but since you won't tell us what that purpose is,
it's pretty hard to help you achieve it. And *that* suggests that you
don't clearly understand, yourself, what that purpose is.

Doug, please see my earlier post. Rankings such as this are quite
common in many different areas.

Rather than tell him why he doesn't need the information, maybe you
could help him with an answer. I'm trying to figure out how to do it
without updating a table each time. It's an interesting problem...

see the reply from nemaC.

so far my tests went, everything was correct.
Or do I miss something ?

regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwK30ACgkQE++2Zdc7Etff+gCdEIHKo6Gf58 3OfMvow3BR6HOk
4SQAn0+jvRElyA6hbOJG0G+k4XQ242gP
=2gzo
-----END PGP SIGNATURE-----

#34

Jerry Stuckle wrote:

Quote:

Doug, please see my earlier post. Rankings such as this are quite
common in many different areas.

Rather than tell him why he doesn't need the information, maybe you
could help him with an answer. I'm trying to figure out how to do it
without updating a table each time. It's an interesting problem...

I already posted a example of generating a position listing using local
variables which I believed that was what OP was after, and not ranking
(e.g. handles the ties in some manner). But I imagine you can adapt the
idea to use an expression to test if the rank value should be
incremented or not based on whether there is a tie, but that would mean
needing two local variables, a incrementing value and a 'flag' that a
tie was found to enable skipping incrementing and then incrementing by
2, all wrapped in a IF().

That would be considerably faster than doing a correlated subqueries, a
traditional SQL solution for ranking, I would think.

#35

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 15:56, Peter H. Coffin wrote:

Quote:

On Mon, 08 Feb 2010 09:17:05 -0500, Jerry Stuckle wrote:
I can think of a lot of reasons why this would be good - for instance,
if the table holds an account balance, where does that customer rank in
the list? Accounting would like to know that.

Or maybe the table (more probably a view) holds the total sales.
Marketing would like to know where that customer ranks in sales.

A lot of reasons why one would want rankings.

And those are perfectly reasonably-described problems. And as such,
they're solveable, eventually, so long as the options of redesigning
tables, creating new tables (permenant or temporary), etc are all on the
table. But the "tell me how I can mimic this feature that SQL doesn't
have to get information I think I want" doesn't get anywhere close to
that.

(Hmm... Now that I'm thinking about rankings tangentially, one of the
questions is going to have to eventually be addressed in the problem
someplace is if you're ranking by val1, out of the table ordered by val1
looking like

name val1
--------------- --------
Bob 3
Trevor 3
Mary 7
Harry 9
Jane 9
Marvin 9
Norman 10
Paul 32
Geoff 103

"What position does Marvin occupy?" and "Who's in the top five?" might
become serious questions with non-obvious answers... )

Thanks for clearing this up.

I first failed to explain but this is exactly the case I have.

regards,
johannes keÃŸler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwLVgACgkQE++2Zdc7EtdwvQCeNUQpsaLmdQ JvVjtrHYW5FzML
Wt4AniDGOjX/A0HT3EUbAAd/Mbb9sWf/
=N1Fh
-----END PGP SIGNATURE-----

#36

On Mon, 08 Feb 2010 16:27:20 +0100, Johannes Keßler wrote:

Quote:

On 08/02/10 15:56, Peter H. Coffin wrote:
(Hmm... Now that I'm thinking about rankings tangentially, one of the
questions is going to have to eventually be addressed in the problem
someplace is if you're ranking by val1, out of the table ordered by val1
looking like

name val1
--------------- --------
Bob 3
Trevor 3
Mary 7
Harry 9
Jane 9
Marvin 9
Norman 10
Paul 32
Geoff 103

"What position does Marvin occupy?" and "Who's in the top five?" might
become serious questions with non-obvious answers... )

Thanks for clearing this up.

I first failed to explain but this is exactly the case I have.

What? Like that sample table? You can join the table to itself with an
inequality and do something like

select count(*)
from my_table a join my_table b on a.val1 >= b.val1
where b.name = 'Marvin' ;

which will retufn 6, meaning that Marvin is no lower than the sixth
place. If you want to be generous and include people at the higher end
of the scale, saying that the group with val1 = 9 are tied for 4th,
you use a > connector in the join instead of >= and add 1 to the count.

For a "top 5" scenario, you first have to decide whether you want to
include all the equal items and potentially end up with more than five
items, or exclude equals and potentially end up with fewer. I find it
easier to think about as a subquery:

select a.name
from count_test a
where a.val1 >= (select min(b.val1)
from (select c.val1
from count_test c
order by val1 desc
limit 5) b
)
;

This one gets the "potentially more items than strict limit" version.
Changing >= to > will result in the "potentially fewer items" version.
Which to choose will probably depend on how expensive your prizes are.

As long as you have indexes properly built, such as making sure to have
one on val1 so that the innermost subquery runs quickly, it shouldn't
take long to run any of these on even a very large table. The optimizer
will, in the second case, very quickly build its small derived table, so
the b query runs quickly (there's only 5 rows to find the min() of), and
that makes the a query part very simple as well.

--
16 megs in a '95 box! Yo Ho Ho and a battle of RAM!

#37

Banana wrote:

Quote:

Jerry Stuckle wrote:
Doug, please see my earlier post. Rankings such as this are quite
common in many different areas.

Rather than tell him why he doesn't need the information, maybe you
could help him with an answer. I'm trying to figure out how to do it
without updating a table each time. It's an interesting problem...

I already posted a example of generating a position listing using local
variables which I believed that was what OP was after, and not ranking
(e.g. handles the ties in some manner). But I imagine you can adapt the
idea to use an expression to test if the rank value should be
incremented or not based on whether there is a tie, but that would mean
needing two local variables, a incrementing value and a 'flag' that a
tie was found to enable skipping incrementing and then incrementing by
2, all wrapped in a IF().

That would be considerably faster than doing a correlated subqueries, a
traditional SQL solution for ranking, I would think.

Not necessarily. Your solution requires retrieving multiple rows of
data which are just thrown away. This probably would be faster if the
user is near the top of the list, but could be slower if the user is far
down the rankings. A subquery would only retrieve the requested row,
but would have to number all rows.

And your code is similar to the idea I have. I, like you, am assuming
there would be no ties - probably not a valid assumption, but it makes
coding easier, at least to start.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#38

Johannes Keßler wrote:

Quote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 16:03, Jerry Stuckle wrote:
Doug Miller wrote:
In article <hkp2tr$rc1$03$2 (AT) news (DOT) t-online.com>,
=?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail (AT) bananas-playground (DOT) net> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 13:51, Doug Miller wrote:
In article <hkov40$tnc$00$1 (AT) news (DOT) t-online.com>,
=?ISO-8859-1?Q?Johannes_Ke=DFler?= <mail (AT) bananas-playground (DOT) net> wrote:
Sorry if this is not as clear as it should be.

here is an example

customerid|name|price|points|sell
- ---------------------------------
1|name1|10|22|1
2|name2|3|1|25
3|name3|15|84|23
4|name4|122|81|22

SELECT * FROM table ORDER BY price DESC

result would be:

customerid|name|price|points|sell
- ---------------------------------
4|name4|122|81|22
3|name3|15|84|23
1|name1|10|22|1
2|name2|3|1|25

Now I want to know where in the list or at what possition a specific
customer
is. If I sort by price, customer 3 is at place 2.
If I sort by points it would be at place 1.

But right now there is no such information at which possition the
customer
is,
I
got this only by numbering the rows manually as described as my
previous
reply.
Now the problem is that everytime I want to get a possiton I need
to query
the
whole table with a different ORDER BY. And then number the rows
manually.
If the table has 10000 rows you have to number 10000 rows. And you
have the
data
from 10000 rows.
Image if the customer is at possition 3 you could only number until
you
reach
this specific customer AND do not have to number all the 10000
rows, just to
find out that the customer is a row 3. numbering all the other 9997
rows is
useless and a wast of data which is pulled from the table.

So my question is if I can create a query which just does this without
getting
all the data.

I hope it is more clear now. But english is not my primary language.
Yes, *that* much is clear. What is still unclear is why you think it
is important to know what position that customer is in the table.
see my reply to Captain Paralytic
Oh, that would be the reply in which you fail -- for the fifth time,
at least -- to answer the question "why do you need this information?"

I'm going to repeat -- ONCE -- the advice I gave you in my first
response, with some elaboration: You almost assuredly do not need to
know this. That you think you do is strongly sugggestive of a flawed
design, and I recommend that you re-examine the entire concept. There
is almost certainly a simpler method of accomplishing your purpose,
whatever it is -- but since you won't tell us what that purpose is,
it's pretty hard to help you achieve it. And *that* suggests that you
don't clearly understand, yourself, what that purpose is.
Doug, please see my earlier post. Rankings such as this are quite
common in many different areas.

Rather than tell him why he doesn't need the information, maybe you
could help him with an answer. I'm trying to figure out how to do it
without updating a table each time. It's an interesting problem...

see the reply from nemaC.

so far my tests went, everything was correct.
Or do I miss something ?

regards,
johannes keßler
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwK30ACgkQE++2Zdc7Etff+gCdEIHKo6Gf58 3OfMvow3BR6HOk
4SQAn0+jvRElyA6hbOJG0G+k4XQ242gP
=2gzo
-----END PGP SIGNATURE-----

Yes, that's a much more elegant solution than the one I was looking at.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#39

Jerry Stuckle wrote:

Quote:

Banana wrote:
That would be considerably faster than doing a correlated subqueries,
a traditional SQL solution for ranking, I would think.

Not necessarily. Your solution requires retrieving multiple rows of
data which are just thrown away. This probably would be faster if the
user is near the top of the list, but could be slower if the user is far
down the rankings. A subquery would only retrieve the requested row,
but would have to number all rows.

Ah, if we didn't want all the rows, sure. I had assumed we would want
all of the rows that was examined to be returned and leave it up to
WHERE clause to tell when to stop examining. For something in the middle
without the leading rows, we'd probably need to look at it differently.

I wouldn't want to get rows just to toss it aside. God get quite irate
when data is wasted, you know.

#40

On Mon, 08 Feb 2010 08:55:46 -0800, Banana wrote:

Quote:

Jerry Stuckle wrote:
Banana wrote:
That would be considerably faster than doing a correlated subqueries,
a traditional SQL solution for ranking, I would think.

Not necessarily. Your solution requires retrieving multiple rows of
data which are just thrown away. This probably would be faster if the
user is near the top of the list, but could be slower if the user is far
down the rankings. A subquery would only retrieve the requested row,
but would have to number all rows.

Ah, if we didn't want all the rows, sure. I had assumed we would want
all of the rows that was examined to be returned and leave it up to
WHERE clause to tell when to stop examining. For something in the middle
without the leading rows, we'd probably need to look at it differently.

I wouldn't want to get rows just to toss it aside. God get quite irate
when data is wasted, you know.

I take it you'll be on the side against premature optimization the next
time some enthusiastic idiot pops in asking which of two near-identical
functions is faster outside of a loop, then? (:

--
77. If I have a fit of temporary insanity and decide to give the hero
the chance to reject a job as my trusted lieutentant, I will retain
enough sanity to wait until my current trusted lieutenant is out of
earshot before making the offer. --Anspach's Evil Overlord List