Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Unanswered: Can't figure out this query

    I have a table with multiple records.

    Some have the same value in the 'subkey' field.
    I want to select all the records from the table that have their highest MAINKEY.

    So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

    Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

    So the tables looks sort of lLike this:

    ID SK MK
    1 10 2
    2 10 3
    3 25 2
    4 25 3

    I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

    I can not figure this out.

    Any help would be GREATLY appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This works....

    Code:
    SELECT [ID]
    FROM yourtable T1
    WHERE EXISTS (
         SELECT  SK, MAX(MK) AS MK
         FROM yourtable T2
         WHERE T1.SK=T2.SK
         GROUP BY SK
         HAVING  T1.MK=MAX(T2.MK))
    Inspiration Through Fermentation

  3. #3
    Join Date
    May 2005
    Posts
    48
    select a.id, a.sk, a.mk from yourtable a
    where a.mk in(select max(b.mk) from yourtable b
    where a.sk = b.sk)

  4. #4
    Join Date
    May 2005
    Posts
    48
    Simpler even:

    select a.sk, max(a.mk) as MK from yourtable a
    group by a.sk

Posting Permissions

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