Query Help 1

[marabozu]

I have a database with two table, lets say table Authors and Table Books. Table Authors have the following information:
-AuthorId- -Author-
AuthorId1, AuthorName1
AuthorId2, AuthorName1
Table Books:
-BookId- -BookName- -Author- -Date-
ID1, Book1, Author1, Date1
ID2, Book2, Author2, Date2
ID3, Book3, Author1, Date3
ID4, Book4, Author2, Date4

I want a SQL query that give me only the latest books writen by the authors. This one:
"SELECT B.BookName FROM B Books, A Authors WHERE A.Author = B.Author order by B.Date DESC"
give me also the older books, but I just want only the latest from each author.
Can anyone help me.
Thank you

 

[LarryDeLaruelle]

Marabozu:

I'm not sure how to do this directly with SQL code but in a qbe you can set the query to a total type (under View, select Totals).

In the Total Line that is presented, set the groupings appropriately (by Author for example) and then select First in the date field on the Totals Line (if you have sorted descending).

After you get this to work you can look at the sql view to see how the code is structured.

Let me know if this works for you.
Larry De Laruelle
larry1de@yahoo.com

 

[marabozu]

Larry De Laruelle,

thank you very much for your help.
I've tried to make the SQL query the way you told me, and it works! The SQL code is:

SELECT Authors.Author AS Authors_Author, Books.Author AS Books_Author,Last(Books.BookName) AS LastOfDt, Last(Books.Dt) AS LastOfDt FROM Authors INNER JOIN Books ON (Authors.Author = Books.Author) GROUP BY Authors.Author, Books.Author HAVING (((Books.BookName)="Volume 1&quot)ORDER BY Authors.Author, Books.Author

As you can see, I used the Last instead of First, because I want the Lastest record insert. I also insert a search string looking for books with "Volume 1" on his name.

Once again thank you
Miguel Neto

 

[LarryDeLaruelle]

You're welcome.

I've gotten a lot of help from this site, so I'm glad when I have an opportunity to also contribute.


Larry De Laruelle
larry1de@yahoo.com

 

[scking]

"SELECT TOP 1 B.BookName FROM B Books, A Authors WHERE A.Author = B.Author order by B.Date DESC"

Steve King