Professional Web Applications Themes

get max/min of subquery - MySQL

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

  1. #1

    Default get max/min of subquery

    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

    gil Guest

  2. #2

    Default Re: get max/min of subquery

    gil wrote: 

    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 -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: get max/min of subquery

    On May 23, 4:30 pm, lark <net> wrote: 









    >
    > 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 -- net
    > To reply to me directly, delete "despam".- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    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

    gil Guest

  4. #4

    Default Re: get max/min of subquery

    On May 23, 4:30 pm, lark <net> wrote: 









    >
    > 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 -- net
    > To reply to me directly, delete "despam".- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    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

    gil Guest

  5. #5

    Default Re: get max/min of subquery

    == Quote from gil (com)'s article 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > > 
    > >
    > > 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 -- net
    > > To reply to me directly, delete "despam".- Hide quoted text -
    > >
    > > - Show quoted text -[/ref]
    > 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[/ref]

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

Similar Threads

  1. Best way to use subquery?
    By greg.scharlemann@gmail.com in forum MySQL
    Replies: 4
    Last Post: November 27th, 01:25 AM
  2. Subquery
    By Ruszaj in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 2nd, 04:50 PM
  3. Help with subquery
    By Nalini in forum Dreamweaver AppDev
    Replies: 3
    Last Post: August 15th, 12:07 PM
  4. How use Subquery better ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 23rd, 07:47 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139