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

Hello everyone,

my "problem": (Those are fake data)

A table with a lot of rows.
To decide at which possition a row is located compared to others, I currently
make a select with an order criterion.

Then I use the programming language to go through the COMPLETE result and create
an associative array in which the key is the identifier for each row and the
value is the possition starting by 1.

Then I get the possition by: echo $array[customerId]

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.

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

iEYEARECAAYFAktv3H0ACgkQE++2Zdc7Etc8wgCdHCMA0KaUlA KTydhNHdq5Skkc
kbwAn0pRB14NqJl7tk74A+Qj1GaBVlVw
=vs/K
-----END PGP SIGNATURE-----

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

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

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

iEYEARECAAYFAktv43gACgkQE++2Zdc7EtdmbACgk3K05HRH6l c4up1MqyMdOEbp
WoMAn0AQp26uEFIKkHFP/hAAa289dJCA
=wYdu
-----END PGP SIGNATURE-----

On 8 Feb, 10:12, Johannes Keßler <m...@bananas-playground.net> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v2.0.14 (GNU/Linux)
>
> iEYEARECAAYFAktv43gACgkQE++2Zdc7EtdmbACgk3K05HRH6l c4up1MqyMdOEbp
> WoMAn0AQp26uEFIKkHFP/hAAa289dJCA
> =wYdu
> -----END PGP SIGNATURE-----

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?

In article <d7b96ffc-7fc4-4c2c-9616-a149aff4c6f4
@b10g2000yqa.googlegroups.com>, paul_lautman@yahoo.com says...
>
> On 8 Feb, 10:12, Johannes Keßler <m...@bananas-playground.net> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > 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
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v2.0.14 (GNU/Linux)
> >
> > iEYEARECAAYFAktv43gACgkQE++2Zdc7EtdmbACgk3K05HRH6l c4up1MqyMdOEbp
> > WoMAn0AQp26uEFIKkHFP/hAAa289dJCA
> > =wYdu
> > -----END PGP SIGNATURE-----
>
> 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?

I agree, could you explain what data you need and what position of
customerId is and should be

In article <hkom9v$s27$03$1@news.t-online.com>, =?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail@bananas-playground.net> wrote:
>
>my "problem": (Those are fake data)
>
>A table with a lot of rows.
>To decide at which possition a row is located compared to others, I currently
>make a select with an order criterion.

The fact that you think you need this is suggestive of a design flaw.

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

On 08/02/10 12:55, Captain Paralytic wrote:
> On 8 Feb, 10:12, Johannes Keßler <m...@bananas-playground.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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

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

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

On 08/02/10 13:11, Doug Miller wrote:
> In article <hkom9v$s27$03$1@news.t-online.com>, =?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail@bananas-playground.net> wrote:
>>
>> my "problem": (Those are fake data)
>>
>> A table with a lot of rows.
>> To decide at which possition a row is located compared to others, I currently
>> make a select with an order criterion.
>
> The fact that you think you need this is suggestive of a design flaw.

This could also be the case, but the possition changes everytime you change the
ORDER BY. This way you can't save the possition in the table
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAktwABkACgkQE++2Zdc7Etds2gCeNTjQ57vtJf X2j5pCvD0O5QE0
sdYAnRv/TMDy31q4mzOhEnXJNTNHU/Xj
=QJcN
-----END PGP SIGNATURE-----

In article <hkov6o$tnc$00$2@news.t-online.com>, =?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail@bananas-playground.net> wrote:

>On 08/02/10 13:11, Doug Miller wrote:
>> In article <hkom9v$s27$03$1@news.t-online.com>,
> =?UTF-8?B?Sm9oYW5uZXMgS2XDn2xlcg==?= <mail@bananas-playground.net> wrote:
>>>
>>> my "problem": (Those are fake data)
>>>
>>> A table with a lot of rows.
>>> To decide at which possition a row is located compared to others, I currently
>>> make a select with an order criterion.
>>
>> The fact that you think you need this is suggestive of a design flaw.
>
>This could also be the case, but the possition changes everytime you change the
>ORDER BY. This way you can't save the possition in the table

Exactly so. Perhaps if you explained why you think you need to know the value
of something that changes constantly, someone might be able to suggest a
better way to do what you're trying to do.

Johannes Keßler wrote:
> 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.

While I have similar concern with Doug - it's not normal for us to be
concerned with row's position and making any kind of dependencies on
this would be a good source of headaches but there is a way to number
the rows:

mysql> set @a=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a:=@a+1 row_pos, name from pet;
+---------+----------+
| row_pos | name |
+---------+----------+
| 1 | Fluffy |
| 2 | Claws |
| 3 | Buffy |
| 4 | Fang |
| 5 | Bowser |
| 6 | Chirpy |
| 7 | Whistler |
| 8 | Slim |
| 9 | Puffball |
+---------+----------+
9 rows in set (0.00 sec)


You can then use the row_pos to tell you which position it comes in. The
caveat is that you have to manually reset the @a every time you re-use
this query... using a stored procedure may be a good way to encapsulate
this.