#1
  1. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13

    getting the id of max(condition)


    how can i do the following?

    table:

    ------------------------
    id | data
    ------------------------
    1 | 10
    2 | 20
    3 | 90
    4 | 50


    I would like to get the id of the highest data.. in this case, I would like my return id = 4

    This is what I tried:

    SELECT id, MAX(data) FROM table

    it doesn't work at all.

    please advice...
    I have yet to find the right way to do it.

    www.genedavinci.com
  2. #2
  3. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    it doesnt work at all doesnt really help me in trying to figure out what is going on. What does the server respond?

    Anyhow try this: SELECT id, MAX(data) FROM table GROUP BY id

    Normally when the previous SQL is ran it would reply that id is not used as part of an aggregate expression or something along those lines.
    - Andreas Koepke

    Koepke Photography

  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select id from t
    where data
     in (select max(data) from t)
  6. #4
  7. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    Using GROUP BY on a unique ID is a much better way than using the subquery. The group by does less load on the database server since its only one SQL statement not 2
    - Andreas Koepke

    Koepke Photography

  8. #5
  9. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    "Attribute id must be GROUPed or used in an aggregate function"...
    I have yet to find the right way to do it.

    www.genedavinci.com
  10. #6
  11. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    ok. thanks for the feedback. here's the enhanced version of my problem.

    Code:
    id | group | data
    --------------------------
    1  | a        | 10
    2  | a        | 20
    3  | a        | 30
    4  | b        | 10
    5  | b        | 20
    6  | b        | 30
    7  | b        | 40
    how can I group by group and yet get the id of the max data?

    many thanks in advanced....
    I have yet to find the right way to do it.

    www.genedavinci.com
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    a.koepke

    Since our queries does not give the same result I don't see what you mean by saying that one is better than the other.

    genedavinci

    What result do want, given the sample data. I don't understand what you mean by group by group.
  14. #8
  15. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    swampBoogie,

    sorry. i would like to have the following as result.

    id | group | data
    --------------------------
    3 | a | 30
    --------------------------
    7 | b | 40
    --------------------------

    where id 3 is have the highest data in group a and id 7 have the highest data in group b...

    thanks.
    I have yet to find the right way to do it.

    www.genedavinci.com
  16. #9
  17. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    First, learn to describe your problem completely in English (or your primary language): "I want the row with the highest value for 'data' within each group." Clear logic starts with a clear description of the problem.

    In situations like this, I find it nice to rely on transitive closure, meaning that relational database systems make it easy to break problems down into small steps.

    So, take the table you posted above:
    Code:
    id | grp      | data
    -----------------------
    1  | a        | 10
    2  | a        | 20
    3  | a        | 30
    4  | b        | 10
    5  | b        | 20
    6  | b        | 30
    7  | b        | 40
    The main problem here is that the value you want to use for your criteria is not sorted. Sorting is often a good first step toward solving a problem.

    So, let's create a view which sorts it for us:

    CREATE VIEW step1 AS
    SELECT id, grp,data FROM main_table ORDER BY grp,data DESC


    Now we have the data nicely ordered within each group by the value of the 'data' column, in descending order:

    Code:
    id | grp      | data
    -----------------------
    3  | a        | 30
    2  | a        | 20
    1  | a        | 10
    7  | b        | 40
    6  | b        | 30
    5  | b        | 20
    4  | b        | 10
    Now, we just have the problem of eliminating all rows from each group except the first one:

    SELECT DISTINCT ON (grp) id,grp,data FROM step1

    And, we get the results:
    Code:
    id | grp      | data
    -----------------------
    3  | a        | 30
    7  | b        | 40
    Make a view out of this, and you can even do further logic with these results, as your database grows.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    SELECT DISTINCT ON (grp) id,grp,data FROM step1
    Yikes! That's some non-standard SQL!
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Yeah, I thought that postgres developers were good and standard abiding guys.

    Code:
    select * from t as tq
    where data
     in (select max(data) from t
      where "group" = tq."group" )
    group is not a good column name.
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    OK, OK (...sheesh...):

    SELECT DISTINCT grp, MAX(data) FROM step1
    GROUP BY grp;


    That do it for you? Is everybody happy now?



    I forgot the DISTINCT ON clause was non-standard. I still think its a cool idea, though. And yes, as you can see from the above query 'group' is a reserved word.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo