Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Unhappy Unanswered: Query highest entries from database - pls read more.....

    Hi folks, sorry for the poor explanation.

    Im using SQL 2000

    I have a database that has a column named 'Initials' in a char in field

    I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this

    Initials Count
    DRT 51
    AMS 49
    JJJ 21
    PLI 10

    Hope u can help, thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try something like
    SELECT Initials, Count(Initials) AS CountOfInitials
    FROM <mytable>
    GROUP BY Initials;

    that should return the number of rows with the same initials,Im not too sure form your question that that is waht you want. if not you may need to rethink & restate what your problem is.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Or perhaps something like this?
    Code:
    SELECT t.initials, t.COUNT
    FROM TABLE t
    WHERE t.COUNT = (SELECT MAX(t1.COUNT) 
                     FROM TABLE t1
                     WHERE t1.initials = t.initials
    		);
    I hope you are sure that two persons don't have identical initials!

Posting Permissions

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