Professional Web Applications Themes

help with query - MySQL

Hi, I need help in writing a query. The following query uses 2 tables and my try is pasted below; I don't understand how to use count, max functions here. "return the name of the airport with the most flights departing from it. Only return one row and one column of data." select a.name from flight_profiles f, airports a where f.flt_from = a.iata_code and f.flt_from order by f.flt_from DESC LIMIT 1; CREATE TABLE `my-database`.`airports` ( `IATA_CODE` varchar(3) default NULL, `NAME` varchar(30) default NULL, `CTY_IATA_CODE` varchar(3) default NULL, ) ENGINE=InnoDB DEFAULT CHT=latin1; CREATE TABLE `my-database`.`flight_profiles` ( `FLP_ID` decimal(8,0) default NULL, `FLT_NUMB` ...

  1. #1

    Default help with query

    Hi,

    I need help in writing a query.

    The following query uses 2 tables and my try is pasted below; I don't
    understand how to use count, max functions here.

    "return the name of the airport with the most flights departing from
    it. Only return one row and one column of data."

    select a.name
    from flight_profiles f, airports a
    where f.flt_from = a.iata_code
    and f.flt_from
    order by f.flt_from DESC
    LIMIT 1;

    CREATE TABLE `my-database`.`airports` (
    `IATA_CODE` varchar(3) default NULL,
    `NAME` varchar(30) default NULL,
    `CTY_IATA_CODE` varchar(3) default NULL,
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    CREATE TABLE `my-database`.`flight_profiles` (
    `FLP_ID` decimal(8,0) default NULL,
    `FLT_NUMB` decimal(4,0) default NULL,
    `SEATS` int(11) default NULL,
    `PRICE_CODE` varchar(3) default NULL,
    `FLT_FROM` varchar(3) default NULL,
    `FLT_TO` varchar(3) default NULL,
    `depart` datetime default NULL,
    `arrive` datetime default NULL
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    Thanks in advance.
    ros

    ros Guest

  2. #2

    Default Re: help with query

    == Quote from ros (com)'s article 

    this is relatively simple:
    select name from airports join flight_profiles on flt_from = iata_code group by
    iata_code order by count(iata_code) desc limit 1;

    hope this helps
    lark Guest

  3. #3

    Default Re: help with query

    On Apr 25, 2:41 pm, lark <net> wrote: 
    >
    > this is relatively simple:
    > select name from airports join flight_profiles on flt_from = iata_code group by
    > iata_code order by count(iata_code) desc limit 1;
    >
    > hope this helps[/ref]

    Thanks Lark for the help. Really appreciate that.

    ros Guest

  4. #4

    Default help with query

    select number,name,age,address from people
    where number in (88,4,2,6,8,1,166,33)

    How I make this to have order by exact numbers as in query. I want
    result to be ordered like this:

    88
    4
    2
    6
    8
    1
    166
    33

    and not

    1
    2
    4
    ......etc

    and not by name or anything else

    How to do this ? Thank you

    Aahz Guest

  5. #5

    Default Re: help with query

    On 16 May, 09:55, Aahz <com> wrote: 

    SELECT
    `number`,
    `name`,
    `age`,
    `address`
    FROM `people`
    WHERE `number` IN (88,4,2,6,8,1,166,33)
    ORDER BY FIND_IN_SET(`number`,'88,4,2,6,8,1,166,33')

    Captain Guest

  6. #6

    Default Re: help with query

    Thank you Captain !

    Captain Paralytic je napisao: 
    >
    > SELECT
    > `number`,
    > `name`,
    > `age`,
    > `address`
    > FROM `people`
    > WHERE `number` IN (88,4,2,6,8,1,166,33)
    > ORDER BY FIND_IN_SET(`number`,'88,4,2,6,8,1,166,33')[/ref]

    Aahz Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 2
    Last Post: August 6th, 07:08 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 AM

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