Professional Web Applications Themes

A basic question: Removing duplicate results from Max function - MySQL

Hi, Say I have a table Job with columns name, date, salary . I want to get the name ,date and salary for the date when that person earned maximum salary. I am using something like SELECT X.name,X.date,X.salary FROM job X WHERE X.salary IN (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name); The problem is ; if a person earns maximum salary on two dates, both of the dates are printed. I just want to get any one of those two rows. I tried SELECT X.name,Min(X.date),X.salary FROM job X WHERE X.salary IN (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name); ...

  1. #1

    Default A basic question: Removing duplicate results from Max function

    Hi,

    Say I have a table Job with columns name, date, salary . I want to get
    the name ,date and salary for the date when that person earned maximum
    salary. I am using something like

    SELECT X.name,X.date,X.salary
    FROM job X
    WHERE X.salary IN
    (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

    The problem is ; if a person earns maximum salary on two dates, both of
    the dates are printed. I just want to get any one of those two rows.
    I tried

    SELECT X.name,Min(X.date),X.salary
    FROM job X
    WHERE X.salary IN
    (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);

    but it gives error.
    Can anybody please suggest a solution?

    Regards,
    Aamir

    aamircheema@gmail.com Guest

  2. #2

    Default Re: A basic question: Removing duplicate results from Max function

    Sounds like you can GROUP BY X.salary?

    [email]aamircheema[/email] wrote:
    > Hi,
    >
    > Say I have a table Job with columns name, date, salary . I want to get
    > the name ,date and salary for the date when that person earned maximum
    > salary. I am using something like
    >
    > SELECT X.name,X.date,X.salary
    > FROM job X
    > WHERE X.salary IN
    > (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
    >
    > The problem is ; if a person earns maximum salary on two dates, both of
    > the dates are printed. I just want to get any one of those two rows.
    > I tried
    >
    > SELECT X.name,Min(X.date),X.salary
    > FROM job X
    > WHERE X.salary IN
    > (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
    >
    > but it gives error.
    > Can anybody please suggest a solution?
    >
    > Regards,
    > Aamir
    >
    Dikkie Dik Guest

  3. #3

    Default Re: A basic question: Removing duplicate results from Max function

    so the first query's correct? so just order by date (either asc or
    desc) and limit 1;
    [email]aamircheema[/email] wrote:
    > Hi,
    >
    > Say I have a table Job with columns name, date, salary . I want to get
    > the name ,date and salary for the date when that person earned maximum
    > salary. I am using something like
    >
    > SELECT X.name,X.date,X.salary
    > FROM job X
    > WHERE X.salary IN
    > (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
    >
    > The problem is ; if a person earns maximum salary on two dates, both of
    > the dates are printed. I just want to get any one of those two rows.
    > I tried
    >
    > SELECT X.name,Min(X.date),X.salary
    > FROM job X
    > WHERE X.salary IN
    > (SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);
    >
    > but it gives error.
    > Can anybody please suggest a solution?
    >
    > Regards,
    > Aamir
    strawberry Guest

Similar Threads

  1. mysql query with duplicate results
    By aniebel in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: July 16th, 02:49 PM
  2. Replies: 0
    Last Post: September 12th, 08:33 PM
  3. removing duplicate lines
    By Andrew Gaffney in forum PERL Beginners
    Replies: 7
    Last Post: December 10th, 11:05 PM
  4. Basic Function Question
    By Andy Hassall in forum PHP Development
    Replies: 3
    Last Post: September 3rd, 03:22 PM
  5. Removing duplicate elements from an XML file
    By Angshuman Guin in forum PERL Modules
    Replies: 1
    Last Post: August 7th, 05:13 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