Tricky (?) SQL Query

Results 1 to 3 of 3

Thread: Tricky (?) SQL Query

  1. #1
    Paul M Guest

    Default Tricky (?) SQL Query

    Hi<BR><BR>I&#039;m querying an Access database to retrieve the most recent 15 records. I can do this using my SQL statement:<BR><BR>SELECT TOP 15 * FROM news ORDER BY thedate DESC<BR><BR>However, what I need to do is somehow modify this so that only records with a unique value in the field "link" are retrieved. Let me clarify - Suppose 3 records in the top 15 have the same value in "link", I only want the most recent one to be returned in my 15. Is this possible? I&#039;m stumped.<BR><BR>Thanks in advance<BR><BR>Paul

  2. #2
    Join Date
    Dec 1969

    Default RE: Tricky (?) SQL Query

    Try this:<BR><BR>SELECT TOP 15 * <BR>FROM news n<BR>WHERE thedate = (SELECT MAX(thedate) FROM news WHERE link =<BR>ORDER BY thedate DESC<BR><BR>Note the use of a table alias (n) in the sub-query - this is a "correlated" sub-query, where the results depend on a value from the outer query.<BR><BR>I&#039;ve just slapped this together and it&#039;s Friday PM, so I could be wildly off the mark...<BR><BR>Dunc<BR>

  3. #3
    Paul M Guest

    Default Appears to work

    Friday PM or not, you know your SQL. First impressions are that this is working just as I wanted. Many thanks.<BR><BR>Paul

Posting Permissions

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