#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    Question for mysql using 2 tables


    The cat (or cats, if there is a tie) who has won the most awards of all the cats (C) from Payroll (PY) dept.

    Payroll dept is from another table which is employees table,
    EMPLOYEES TABLE:
    +------+------------+----------+------+-----------+
    | ENo | LName | FName | Dept | Salary |
    +------+------------+----------+------+-----------+
    | 1076 | O'Reilly | Walter | SL | 31900.00 |
    | 1087 | Dancer | April | PY | 65000.00 |
    | 1123 | MacMaster | Ben | MK | 83100.00 |
    | 1124 | Klinger | Maxwell | IT | 32080.00 |
    | 1127 | Bellingham | Myrtle | PY | 65000.00 |
    | 1129 | Bell | Fiona | IT | 34100.98 |
    | 1166 | Howell | Lovey | SL | 30100.29 |
    | 1167 | Winchester | Charles | SL | 30900.99 |
    | 1179 | Muldoon | Francis | HR | 20900.00 |
    | 1180 | Douglas | Ernest | AC | 109000.00 |
    | 1182 | Glympshire | Herman | MK | 118700.59 |
    | 1183 | Toddy | Gunther | HR | 21100.55 |
    | 1187 | Smacker | Woodrow | AC | 42080.00 |
    | 1189 | Bellingham | Lester | PY | 37000.50 |
    | 1193 | Moto | Kentaro | IT | 28000.00 |
    | 1194 | Hinkley | Roy | IT | 53400.80 |
    | 1196 | Archer | Morton | SL | 23100.00 |
    | 1197 | Summers | Marianne | MK | 23000.00 |
    | 1201 | McElreath | James | PY | 30000.00 |
    | 1203 | Bell | Jorge | SL | 23000.00 |
    | 1204 | Chan | Charles | HR | 28700.00 |
    | 1205 | Kuryakin | Ilya | AC | 34080.00 |
    | 1208 | Hilton | Susan | PY | 38080.00 |
    | 1233 | McArthur | James | IT | 45080.87 |
    | 1234 | Macafee | Earl | SL | 43000.00 |
    | 1235 | Grant | Ginger | AC | 45600.90 |
    | 1238 | McElroy | Arnold | IT | 45080.00 |
    | 1242 | Bellini | Giorgio | MK | 21900.00 |
    | 1243 | Spade | Samuel | IT | 26500.00 |
    | 1244 | Del Bello | Adam | HR | 56800.00 |
    | 1246 | Blake | Henry | IT | 24000.00 |
    | 1247 | Solo | Napoleon | SL | 24100.88 |
    | 1249 | Grumby | Jonas | PY | 24900.00 |
    | 1250 | Pierce | Benjamin | AC | 54170.90 |
    | 1252 | Mackle | Fearghal | IT | 33190.00 |
    | 1253 | Bell | Gertrude | SL | 43000.00 |
    | 1254 | Hoolihan | Margaret | IT | 34200.80 |
    | 1255 | Potter | Sherman | AC | 34620.00 |
    | 1257 | Archer | Miles | SL | 22450.20 |
    | 1258 | Smacker | Wilton | AC | 46000.00 |
    | 1261 | Hunnicut | Beejay | HR | 23000.00 |
    | 1262 | Smart | Maxwell | AC | 36500.95 |
    | 1263 | Marlowe | Philip | MK | 34300.08 |
    | 1264 | Burns | Francis | MK | 67535.80 |
    +------+------------+----------+------+-----------+


    PETS TABLE:
    +------+------------+------+------+-----+--------+
    | PNo | Name | ENo | Type | Sex | Awards |
    +------+------------+------+------+-----+--------+
    | P101 | Snoopy | 1264 | D | M | 3 |
    | P102 | Rover | 1204 | D | M | 5 |
    | P103 | Garfield | 1261 | C | M | 21 |
    | P104 | Piewacket | 1087 | C | F | 6 |
    | P105 | Toby | 1124 | H | F | 9 |
    | P106 | Rex | 1087 | D | M | 37 |
    | P107 | Oreo | 1124 | C | M | 0 |
    | P108 | Fedallah | 1167 | F | F | 55 |
    | P109 | Winston | 1264 | D | M | 5 |
    | P110 | Bill | 1204 | C | M | 0 |
    | P111 | Opie | 1204 | D | M | 13 |
    | P112 | Lassie | 1257 | D | F | 40 |
    | P113 | Duke | 1264 | D | M | 41 |
    | P114 | Tramp | 1257 | D | M | 13 |
    | P115 | Athlone | 1087 | C | M | 34 |
    | P116 | Hammy | 1257 | H | F | 5 |
    | P117 | Bullett | 1261 | D | M | 19 |
    | P118 | Buttermilk | 1204 | C | F | 4 |
    | P119 | Sylvester | 1247 | C | M | 5 |
    | P120 | Twisty | 1124 | F | M | 1 |
    | P121 | Coolio | 1263 | C | M | 6 |
    | P122 | Frankie | 1124 | F | M | 0 |
    | P123 | Basil | 1249 | C | F | 7 |
    | P124 | Oberon | 1124 | C | F | 0 |
    | P125 | Blue | 1263 | D | M | 0 |
    | P126 | Speedy | 1246 | C | M | 5 |
    | P127 | Tugger | 1087 | D | M | 5 |
    | P128 | Beavis | 1257 | C | M | 98 |
    | P129 | Carlos | 1257 | C | M | 6 |
    | P130 | Beethoven | 1249 | D | M | 3 |
    | P131 | Mephisto | 1124 | C | M | 4 |
    | P132 | Rambo | 1262 | C | M | 6 |
    | P133 | Long John | 1246 | D | M | 3 |
    | P134 | Esmerelda | 1204 | C | F | 36 |
    | P135 | Sharina | 1208 | C | F | 37 |
    | P136 | Myrtle | 1247 | C | F | 0 |
    | P137 | Bugsy | 1261 | C | M | 2 |
    | P138 | Ginger | NULL | C | F | 0 |
    | P139 | Tashtego | 1263 | C | F | 2 |
    | P140 | Billy | 1255 | F | M | 3 |
    | P141 | Dylan | 1167 | D | M | 2 |
    | P142 | Flywheel | 1124 | C | F | 6 |
    | P143 | Scout | 1246 | D | F | 5 |
    | P144 | Elvis | 1250 | D | M | 2 |
    | P145 | Bratwurst | 1205 | D | F | 1 |
    | P146 | Pericles | 1205 | D | M | 1 |
    | P147 | Foghorn | NULL | H | M | 0 |
    | P148 | Zanzibar | 1250 | C | M | 8 |
    +------+------------+------+------+-----+--------+
    48 rows in set (0.00 sec)


    I did select name, awards, e.dept from pets p, employees e where e.eno = p.eno and dept = 'py' and type = 'c';
    and got
    +-----------+--------+------+
    | name | awards | dept |
    +-----------+--------+------+
    | Piewacket | 6 | PY |
    | Athlone | 34 | PY |
    | Basil | 7 | PY |
    | Sharina | 37 | PY |
    +-----------+--------+------+
    4 rows in set (0.00 sec)

    when I do select max(awards) it only gives the max awards and displays Piewacket as the name of the pet, I'm confused about how to make SHarina display as the name for the 37 awards... If anyone can help me, am I overthinking it? Can't seem to get the full row to work together.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    please have a look at this article -- groupwise max
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo