SELECT until

#21

On Mon, 08 Feb 2010 14:16:58 +0100, Johannes Keßler wrote:

Quote:

Question is:

You have a single customer (identified by the customer id) and want to
know at which place (perhaps this is the better word ?) it is by the
specified ORDER BY.

Go back to what Captain P said to you: You are asking about your
solution again, not your problem. A problem is "I want to find the ten
lowest active customer numbers so I can tell the customer if he is one
of those" or "I want to tell the customer how many similar products cost
more than what he bought". Don't describe this with SQL, don't use SQL
terminology, don't even *think* about SQL for this.

Quote:

Since I do not store the place, I have to generate it every time I
want this information by numbering the rows manually and then display
the number which has the row with the correct customer id

You WILL have to generate it every time, as described. Or stick it in a
freshly-created table with an auto-increment value index so that you can
keep the same sort for a while. It's a database, after all. Things are
expected to change in it both frequently and rapidly.

--
The Write Many, Read Never drive. For those people that don't know
their system has a /dev/null already.
-- Rik Steenwinkel, singing the praises of 8mm Exabytes

#22

Johannes Keßler wrote:

Quote:

Because it is nice to have the information where you are to a given ORDER BY and
do not need to see the complete overview and have to look where you are.

it is not about if this is really usefull or not, it is about if there is a good
way to achieve this.

So basically you want to be able to display the list with a number
marking the position of a row? Well, that's doable- I already showed you
how to do this in my first reply.

But do realize this. If there's a website listing customers with a
number for their position and the person calls in and ask for more data
on customer #123456, you couldn't be absolutely certain that when you
select the customer positioned at 123456, it's the same customer that
the user was viewing on the site (what if a customer was inserted or
deleted and thus changed the positioning?). For that reason, it usually
makes sense to show the ID or at least a mean of uniquely identifying
the customer that wouldn't be subject to arbitrary changes when the
query is re-run. This is why as I said earlier, using the positioning
will create more headaches and problems when users start to depend on
it, not understanding that it is unreliable.

#23

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

Quote:

-----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.

#24

Captain Paralytic wrote:

Quote:

On 8 Feb, 13:16, Johannes Keßler <m... (AT) bananas-playground (DOT) net> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/02/10 13:49, Captain Paralytic wrote:

On 8 Feb, 12:12, Johannes Keßler <m... (AT) bananas-playground (DOT) net> wrote:
On 08/02/10 12:55, Captain Paralytic wrote:
On 8 Feb, 10:12, Johannes Ke ler <m... (AT) bananas-playground (DOT) net> wrote:
On 08/02/10 11:08, nemaC wrote:
Is there a way I need only select the rows until the customerid ?
eg.
SELECT * FROM table UNTIL customerId = 22
ORDER BY price DESC
with that I do not need to get all the data from the table.
SELECT <field you need> FROM table WHERE custumerId=22; ??
No.
I get the possition from counting the whole result table manually
eg.
$i=1;
foreach($result as $entry) {
$newArray[$entry['customerId']] = $i;
$i++;
}
The possition is not stored in the database, and changes everytime you alter the
"order by"
regards,
johannes ke ler
I really don't understand what you are after. As you point out, there
is no such thing as a "position" of a row in a table.
Maybe if you told us what you ultimately want to achieve by this?
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.
regards,
johannes ke ler
You still haven't answered my question. I asked "what you ultimately
want to achieve by this?". By that I mean please explain WHY you need
this information? What good is it intended to do you? What process is
this supposed to achieve?
It feels like you have a faulty design somewhere. If you explain what
you ultimately want to achieve, we might be able to help correct it.
Question is:

You have a single customer (identified by the customer id) and want to know at
which place (perhaps this is the better word ?) it is by the specified ORDER BY.

Since I do not store the place, I have to generate it every time I want this
information by numbering the rows manually and then display the number which has
the row with the correct customer id

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwDsoACgkQE++2Zdc7EtdjqwCfX8DlsHWBeK 3RYVPV+L6DKJtQ
W5wAoI1vjwMmMWffCClr8g8K3PohXhoM
=3V4n
-----END PGP SIGNATURE-----

I am near to giving up on you in this thread. I and others keep asking
you why you think you need this particular piece of information and
all you keep repeating is that you want to know the place.

One more chance. WHY DO YOU THINK YOU NEED THIS INFORMATION?
WHAT GOOD IS IT INTENDED TO DO YOU?
WHAT PROCESS IS THIS INTENDED TO SUPPORT?

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.

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

#25

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

On 08/02/10 15:17, Jerry Stuckle wrote:

Quote:

Captain Paralytic wrote:
On 8 Feb, 13:16, Johannes Keßler <m... (AT) bananas-playground (DOT) net> wrote:
On 08/02/10 13:49, Captain Paralytic wrote:

On 8 Feb, 12:12, Johannes Keßler <m... (AT) bananas-playground (DOT) net> wrote:
On 08/02/10 12:55, Captain Paralytic wrote:
On 8 Feb, 10:12, Johannes Ke ler <m... (AT) bananas-playground (DOT) net
wrote:
On 08/02/10 11:08, nemaC wrote:
Is there a way I need only select the rows until the
customerid ?
eg.
SELECT * FROM table UNTIL customerId = 22
ORDER BY price DESC
with that I do not need to get all the data from the table.
SELECT <field you need> FROM table WHERE custumerId=22; ??
No.
I get the possition from counting the whole result table manually
eg.
$i=1;
foreach($result as $entry) {
$newArray[$entry['customerId']] = $i;
$i++;
}
The possition is not stored in the database, and changes
everytime you alter the
"order by"
regards,
johannes ke ler
I really don't understand what you are after. As you point out,
there
is no such thing as a "position" of a row in a table.
Maybe if you told us what you ultimately want to achieve by this?
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.
regards,
johannes ke ler
You still haven't answered my question. I asked "what you ultimately
want to achieve by this?". By that I mean please explain WHY you need
this information? What good is it intended to do you? What process is
this supposed to achieve?
It feels like you have a faulty design somewhere. If you explain what
you ultimately want to achieve, we might be able to help correct it.
Question is:

You have a single customer (identified by the customer id) and want
to know at
which place (perhaps this is the better word ?) it is by the
specified ORDER BY.

Since I do not store the place, I have to generate it every time I
want this
information by numbering the rows manually and then display the
number which has
the row with the correct customer id

I am near to giving up on you in this thread. I and others keep asking
you why you think you need this particular piece of information and
all you keep repeating is that you want to know the place.

One more chance. WHY DO YOU THINK YOU NEED THIS INFORMATION?
WHAT GOOD IS IT INTENDED TO DO YOU?
WHAT PROCESS IS THIS INTENDED TO SUPPORT?

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.

thanks,

I was close to give up, since my mother language is not english...

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwHUoACgkQE++2Zdc7EtctGQCcC14gp5oojl p0wrDQ6X1qonEI
HsAAoIwYsLLu6WI20Q6DeN2mpMcrXhcZ
=urWi
-----END PGP SIGNATURE-----

#26

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

On 08/02/10 15:12, Doug Miller wrote:

Quote:

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.

err..
I don't know which client you have but I can see all the other responses others
have made.
And I always read them first, before I respond and I respond only once and not
for every use seperately, since everybody can see my response.
I respond to the newsgroup.

This is not to be ment offensive, but I wonder why you did not see my other
responses.

Or do I something wrong.

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

iEYEARECAAYFAktwHigACgkQE++2Zdc7Etca7ACfZdkIv9Ope+ wiJb1qu4y/Yf7x
pJwAniveVE7lDKSd++kU7RuhPPEpMEha
=K9k2
-----END PGP SIGNATURE-----

#27

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

On 08/02/10 14:57, Peter H. Coffin wrote:

Quote:

On Mon, 08 Feb 2010 14:16:58 +0100, Johannes Keßler wrote:

Question is:

You have a single customer (identified by the customer id) and want to
know at which place (perhaps this is the better word ?) it is by the
specified ORDER BY.

Go back to what Captain P said to you: You are asking about your
solution again, not your problem. A problem is "I want to find the ten
lowest active customer numbers so I can tell the customer if he is one
of those" or "I want to tell the customer how many similar products cost
more than what he bought". Don't describe this with SQL, don't use SQL
terminology, don't even *think* about SQL for this.

Since I do not store the place, I have to generate it every time I
want this information by numbering the rows manually and then display
the number which has the row with the correct customer id

You WILL have to generate it every time, as described. Or stick it in a
freshly-created table with an auto-increment value index so that you can
keep the same sort for a while. It's a database, after all. Things are
expected to change in it both frequently and rapidly.

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.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwIJEACgkQE++2Zdc7Etf2cQCfWLP9mLAKNK Ru9ZjHl6fMLJrb
96IAniw+ZBXfR2OT26GsdYkgo65EJcl6
=zFcD
-----END PGP SIGNATURE-----

#28

On Mon, 08 Feb 2010 09:17:05 -0500, Jerry Stuckle wrote:

Quote:

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... )

--
54. I will not strike a bargain with a demonic being then attempt to
double-cross it simply because I feel like being contrary.
--Peter Anspach's list of things to do as an Evil Overlord

#29

On Mon, 08 Feb 2010 15:32:49 +0100, Johannes Keßler wrote:

Quote:

On 08/02/10 14:57, Peter H. Coffin wrote:

On Mon, 08 Feb 2010 14:16:58 +0100, Johannes Keßler wrote:

Question is:

You have a single customer (identified by the customer id) and want
to know at which place (perhaps this is the better word ?) it is by
the specified ORDER BY.

Go back to what Captain P said to you: You are asking about your
solution again, not your problem. A problem is "I want to find the
ten lowest active customer numbers so I can tell the customer if he
is one of those" or "I want to tell the customer how many similar
products cost more than what he bought". Don't describe this with
SQL, don't use SQL terminology, don't even *think* about SQL for
this.

Since I do not store the place, I have to generate it every time
I want this information by numbering the rows manually and then
display the number which has the row with the correct customer id

You WILL have to generate it every time, as described. Or stick it in
a freshly-created table with an auto-increment value index so that
you can keep the same sort for a while. It's a database, after all.
Things are expected to change in it both frequently and rapidly.

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.

Maybe. But we can't tell because we don't know what problem you're
trying to solve.

--
71. If I decide to test a lieutenant's loyalty and see if he/she should
be made a trusted lieutenant, I will have a crack squad of marksmen
standing by in case the answer is no.
--Peter Anspach's list of things to do as an Evil Overlord

#30

Doug Miller wrote:

Quote:

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...

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