SELECT until

#11

Op 8-2-2010 13:12, Johannes Keßler schreef:

Quote:

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 can add a field position in the table, and do this:

set @i=0; update `table` set position=@i:=@i+1 ORDER BY `whatever`;

After that you can do:
SELECT position FROM table WHERE customerId = 22

But it is still confusing what you want to do with this 'position' field...

And, above 'solution' will NOT be multi-user....

And, above 'solution' WILL do that, what you dont want to do ... ;-)
(because its of no use, given the arguments sofar)

--
Luuk

#12

Johannes Keßler wrote:

Quote:

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 wonder if your requirement could have been satisfied by doing a LIMIT
<offset>, <number of rows> with your ORDER BY? That way you could get
the paging functionality and you don't have to care about how many rows
there are - keep paging (incrementing the offset with numbers of rows)
until it exceeds the count of the rows?

#13

On 8 Feb, 12:12, Johannes Keßler <m... (AT) bananas-playground (DOT) net> wrote:

Quote:

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

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

iEYEARECAAYFAktv/78ACgkQE++2Zdc7EtdkAQCeNaAUcqqi5sTl+7fcH7MRObWa
xLYAniq/Tg6mzqbPeJmRgWb00wvqq5De
=4pBj
-----END PGP SIGNATURE-----

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.

#14

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:

Quote:

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.

#15

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

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

Quote:

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

#16

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

On 08/02/10 13:51, Doug Miller wrote:

Quote:

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

iEYEARECAAYFAktwDv0ACgkQE++2Zdc7EtcdkQCfWzsrZKLjt5 0NTa+0k+JTaLeX
qHoAn0DoIT0Z9ltuyDMNsN4rrE0w7Gy2
=oO6J
-----END PGP SIGNATURE-----

#17

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.

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

But *why* do you need that info? Why should it matter if the customer is
positioned first, somewhere in middle or last in the displayed last?

For example, if you display a list of customers and you want the users
to get more detail by double clicking on a certain row- you use the
customer ID not the position. So that's why we're all wondering why you
think you need to know what a position a customer is in.

#18

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

On 08/02/10 14:24, Banana wrote:

Quote:

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.

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

But *why* do you need that info? Why should it matter if the customer is
positioned first, somewhere in middle or last in the displayed last?

For example, if you display a list of customers and you want the users
to get more detail by double clicking on a certain row- you use the
customer ID not the position. So that's why we're all wondering why you
think you need to know what a position a customer is in.

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

iEYEARECAAYFAktwFMQACgkQE++2Zdc7EtfRygCfWxxOJh0xYm ABSo5oOxdTkE9f
cxAAn3WPzfhBxDgBEL+LiBx2po3vRBaj
=k+Mz
-----END PGP SIGNATURE-----

#19

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

Quote:

-----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 havethe data
from 10000 rows.

Image if the customer is at possition 3 you could only number until youreach
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?

#20

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

On 08/02/10 14:44, Captain Paralytic wrote:

Quote:

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?

See my reply from 14:42 or 2:42PM since there I've already answered..
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwGAIACgkQE++2Zdc7EtdtJQCbBhybV/083T34hL9XTsK5zY63
LQ4AmQE73mLBw4/5FzaaTALN3NtkNAR+
=XX7d
-----END PGP SIGNATURE-----