SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    is a subselect appropriate here? if so how?

    hi again everyone i've another query for all you grizzled db veterans around here

    in english, what i'm trying to do is to determine the row with the maximum no. of no_beers and no_ciders (purely imaginary names to mask the real names... i don't really have a beer database )... from this row, i want to further know the difference between 2 other datetime fields (time_start and time_end)... i'm not sure how to accomplish that and have never written a join (beyond the simple joins using where clauses) nor a subselect (which wouldn't be appropriate here i think)...

    here's what i've:
    Code:
    SELECT
     some_no,
     MAX(no_beers + no_ciders) AS longest
    FROM
     mydb.mytbl
    WHERE
     vin=27723
    GROUP BY
     some_no
    and this is what it turns up:
    Code:
       SOME_N          LONGEST
    --------- ----------------
          204               16
          205               12
    now what i want is to find out the difference between the time_start and time_end fields of the row with SOME_N = 204 and LONGEST = 16

    <edit> i'm using oracle, forgot to add </edit>

    thanks a lot!

    - Joel

    Footnote:
    Table structure:
    Code:
    some_n		| NUMBER(3)
    no_beers	| NUMBER(2)
    no_ciders	| NUMBER(2)
    time_start	| DATE
    time_end	| DATE
    Last edited by redemption; Jul 22, 2002 at 00:59.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hi joel
    Code:
    SELECT some_no
         , time_end - time_start
         , no_beers + no_ciders
      FROM mydb.mytbl
     WHERE vin=27723
       AND no_beers + no_ciders =
           ( SELECT MAX(no_beers + no_ciders) 
               FROM mydb.mytbl
              WHERE vin=27723 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •