Page 1 of 3 123 LastLast
Results 1 to 15 of 31

Thread: query help

  1. #1
    Join Date
    Aug 2010
    Posts
    13

    Unanswered: query help

    Hi,

    I have a question regarding filtering records. Kindly please help.
    I have the following table.

    Table: emp_monthly_sales
    emp_id|date_time|sales_amount
    101|20100105|250000
    102|20100118|750000
    101|20100409|1800000

    Now I just want an SQL query to pick records of each employee's latest sales.
    So the query result should be like the following.

    102|20100118|750000
    101|20100409|1800000

    Here emp_id is the foreign key.

    Thank you.

    Regards,
    Sakitram

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Why on earth would we want to do your coursework?

  3. #3
    Join Date
    Aug 2010
    Posts
    13
    Hi, it's not my coursewoork, but in just asked a way how to do a task in database in my profession. Please help.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakitram View Post
    Please help.
    sure, be glad to

    we'll do it in two stages

    first, please write a query that returns the latest date for each employee
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2010
    Posts
    13
    SELECT max(date_time) FROM emp_monthly_sales

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    that's actually the latest date for all employees.

  7. #7
    Join Date
    Aug 2010
    Posts
    13
    SELECT max(date_time) FROM emp_monthly_sales group by emp_id.

    I think the answer is the following.

    SELECT a.emp_id, max(b.date_time), b.sales_amount
    FROM emp_monthly_sales a, emp_monthly_sales b
    WHERE a.emp_id=b.emp_id
    GROUP BY a.emp_id

    I'm not sure.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by sakitram View Post
    I'm not sure.
    The simple way to see if you're correct is to run the query.
    Do you get the latest date for each employee?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakitram View Post
    SELECT max(date_time) FROM emp_monthly_sales group by emp_id.
    this is syntactically correct, but not semantically

    it does give you a result set of dates, and they do happen to be the last date for each employee, however, all you get is the dates... so this query has a very serious logical shortcoming -- you can't tell whose dates they are!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2010
    Posts
    13
    sorry... that did not give result what I wanted..
    please correct the query.. thanks.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakitram View Post
    please correct the query.. thanks.
    how about if i teach you how to fix it

    let's do this in two stages

    first, please write a query that returns the latest date for each employee
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2010
    Posts
    13
    Quote Originally Posted by r937 View Post
    first, please write a query that returns the latest date for each employee
    here is the query

    SELECT a.emp_id, max(b.dt)
    FROM tmp a, tmp b
    WHERE a.emp_id=b.emp_id
    GROUP BY a.emp_id

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakitram View Post
    here is the query
    now we're making progress

    before going to stage 2, i wonder if i could ask you to please rewrite this query using JOIN syntax instead of the implicit comma join you have now

    also, would you please assign a column alias to the MAX expression
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Aug 2010
    Posts
    13
    Quote Originally Posted by r937 View Post
    before going to stage 2, i wonder if i could ask you to please rewrite this query using JOIN syntax instead of the implicit comma join you have now

    also, would you please assign a column alias to the MAX expression
    SELECT a.emp_id, max(b.dt) sales_date
    FROM tmp a left join tmp b
    on a.emp_id=b.emp_id
    GROUP BY a.emp_id

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my sincere apologies, i was so anxious to move on with this that i only briefly examined your last query, and sort of automatically asked you to rewrite the join (because i won't work with anything except explicit JOIN syntax)

    but now i need for you to go back to square one, as it were, and write a brand new query that gets the latest sales date for each employee...

    ... except that there will be only one table involved in the query, namely, the single table that you talked about in your first post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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