On May 23, 4:30 pm, lark <ham...@sbcdeglobalspam.net> wrote:
> gil wrote:
> > hi,
>
> > i have a table with a column that is a scalar ("alert_key") .
>
> > my subquery is:
>
> > SELECT alert_key FROM `user` WHERE alert_key < 4000 ORDER BY alert_key
> > DESC LIMIT 100
>
> > this brings me 100 or less rows. now i need to find the minimum value
> > from the returned rows and with that value (call it MinValue) do
> > another SELECT :
>
> > SELECT alert_key FROM `user` WHERE alert_key > MinValue ORDER BY
> > alert_key ASC LIMIT 100
>
> > this looks kind of stupid, but the reason for this is that i allways
> > want to have 100 rows, therefore , if the first SELECT got only 50
> > rows, i want to take thes 50 rows + 50 more rows that are not <4000.
>
> > bottom line: how do i get the MIN of a subquery into a variable , all
> > in one statement.
>
> > thanks a lot,
>
> > gil
>
> have you tried something like this:
>
> SELECT alert_key FROM `user` WHERE alert_key > (SELECT min(alert_key)
> FROM `user` WHERE alert_key < 4000) ORDER BY
> alert_key ASC LIMIT 100
>
> --
> lark -- ham...@sbcdeglobalspam.net
> To reply to me directly, delete "despam".- Hide quoted text -
>
> - Show quoted text -

thanks for your reply.

this still doesn't solve my problem because it will allways bring the
minimum of all rows that are <4000, and not the minimum of the 100
rows smaller than 4000 order by alert_key DESC.this is why i need the
minimum of the subquery.

do you have another idea?

thanks again,

gil

== Quote from gil (EMAIL REMOVED)'s article
> On May 23, 4:30 pm, lark <ham...@sbcdeglobalspam.net> wrote:
> > gil wrote:
> > > hi,
> >
> > > i have a table with a column that is a scalar ("alert_key") .
> >
> > > my subquery is:
> >
> > > SELECT alert_key FROM `user` WHERE alert_key < 4000 ORDER BY alert_key
> > > DESC LIMIT 100
> >
> > > this brings me 100 or less rows. now i need to find the minimum value
> > > from the returned rows and with that value (call it MinValue) do
> > > another SELECT :
> >
> > > SELECT alert_key FROM `user` WHERE alert_key > MinValue ORDER BY
> > > alert_key ASC LIMIT 100
> >
> > > this looks kind of stupid, but the reason for this is that i allways
> > > want to have 100 rows, therefore , if the first SELECT got only 50
> > > rows, i want to take thes 50 rows + 50 more rows that are not <4000.
> >
> > > bottom line: how do i get the MIN of a subquery into a variable , all
> > > in one statement.
> >
> > > thanks a lot,
> >
> > > gil
> >
> > have you tried something like this:
> >
> > SELECT alert_key FROM `user` WHERE alert_key > (SELECT min(alert_key)
> > FROM `user` WHERE alert_key < 4000) ORDER BY
> > alert_key ASC LIMIT 100
> >
> > --
> > lark -- ham...@sbcdeglobalspam.net
> > To reply to me directly, delete "despam".- Hide quoted text -
> >
> > - Show quoted text -
> thanks for your reply.
> this still doesn't solve my problem because it will allways bring the
> minimum of all rows that are <4000, and not the minimum of the 100
> rows smaller than 4000 order by alert_key DESC.this is why i need the
> minimum of the subquery.
> do you have another idea?
> thanks again,
> gil

ah, how about this:

SELECT alert_key from user WHERE alert_key > min(SELECT (alert_key) FROM user
where alert_key < 4000 LIMIT 100) order by alert_key asc limit 100

--
POST BY: PHP News Reader