Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Unanswered: Help with sorting strings...

    I'm trying to search a database and get a list of results of
    the latest values which are of type 'STRING'. How would I do it?

    For instance, I've got a dataset like the one below.

    Col 1 Col 2 Col 3
    ------------------------------------------------
    Dog Blue 11a
    Dog Blue 11b
    Cat Blue 14
    Cat Red 21a
    Cat Red 21b
    Fish Yellow 31
    Shark Black 12a
    Shark Purple 21

    I only want it to return the ones with the highest 'Col 3' value, so it returns something like.

    Col 1 Col 2 Col 3
    ------------------------------------------------------
    Dog Blue 11b
    Cat Red 21b
    Fish Yellow 31
    Shark Purple 21

    I've tried something like this:

    SELECT
    table.col1,
    table.col2,
    table.col3
    FROM
    table
    WHERE
    1 > (
    SELECT
    COUNT(DISTINCT table.col3)
    FROM
    table tab
    WHERE
    tab.col3 > table.col3
    )

    However I get the ERR: An aggregate may not appear in the WHERE clause
    unless it is in a subquery contained in a HAVING clause or select
    list, and the column being aggregated is an outer reference.

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    I don't what your backend database is but this should help.
    Code:
    SELECT t.col1
         , t.col2
         , t.col3
    FROM tablex t
     , (SELECT tablex.col1
             , max (tablex.col3) col3
        FROM tablex
        group by col1) g
    WHERE t.col1 = g.col1
    AND t.col3 = g.col3
    ;
    or using ANSI joins
    Code:
    SELECT t.col1
         , t.col2
         , t.col3
    FROM tablex t
    JOIN (SELECT tablex.col1
             , max (tablex.col3) col3
        FROM tablex
        group by col1) g
    ON t.col1 = g.col1
    AND t.col3 = g.col3
    ;

  3. #3
    Join Date
    Mar 2005
    Posts
    5
    Thanks for your help gannet.

Posting Permissions

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