How can I?

Results 1 to 2 of 2

Thread: How can I?

  1. #1
    Join Date
    Dec 1969

    Default How can I?

    I was hoping how I can do the following. I wish to use Count to see how many records are found with a particular search. e.g First Name of John. Now there may be say 20 Johns' however I only wish to present the user with with the name John who has the highest ID. e.g. If one John had an ID of 22 and the others has say ID 34, ID, 45 and so forth, it should only show one John. Is that possible and if so how can I do so?

  2. #2
    Join Date
    Dec 1969

    Default Trivial...

    SELECT COUNT(*) AS TheCount, Max(ID) AS TheID<BR>FROM table<BR>WHERE name = &#039;John&#039;<BR><BR>Now, that will only get you the ID of the highest John. You&#039;ll need another query then to get the full info on him:<BR><BR>SELECT * FROM table WHERE ID = ###<BR><BR>With SQL Server, I *think* you could get both at the same time:<BR><BR>SELECT T1.*, T2.theCount <BR>FROM table AS T1, ( SELECT COUNT(*) AS theCount, MAX(ID) as theID FROM table ) AS T2<BR>WHERE T1.ID = T2.theID<BR><BR>

Posting Permissions

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