SQL: Select last records of the year
Hi, would anybody help with this SQL question:
Suppose there is a table with two fields for the name of the booksp publisehd
and the date/time the books are published. Assume there are N ( N = 20,
for example) books published over ten years period. So In certain year(s),
there are more than one book published. Further assume don't know the range
of the years. Can we construct an SQL to select only one book from a year
(if there is a book in that year, of course) and if there are more than one
books publishd in a year, then the SQL should pick only the last book published
in that year?
The problem is that the query depends on the "dynamic" properties
of the resulting records selected by SQL. That is, we don't know how many
books been published in one year (anywhere from 0 to some number). We cannot
use >=1990 or <= 2002, etc. Second, we not only select only one, but it must
be the last one in date/time.
Thanks a lot in advance.
John
[1046 byte] By [
John] at [2007-11-9 19:34:38]
# 1 Re: SQL: Select last records of the year
"John" <John.Guo@Ametek-Online.com> wrote in
news:3f453f8d$1@tnews.web.dev-archive.com:
>
> Hi, would anybody help with this SQL question:
>
> Suppose there is a table with two fields for the name of the booksp
> publisehd
> and the date/time the books are published. Assume there are N ( N =
> 20,
> for example) books published over ten years period. So In certain
> year(s), there are more than one book published. Further assume don't
> know the range of the years. Can we construct an SQL to select only
> one book from a year (if there is a book in that year, of course) and
> if there are more than one books publishd in a year, then the SQL
> should pick only the last book published in that year?
>
>
> The problem is that the query depends on the "dynamic" properties
> of the resulting records selected by SQL. That is, we don't know how
> many
>
> books been published in one year (anywhere from 0 to some number). We
> cannot use >=1990 or <= 2002, etc. Second, we not only select only
> one, but it must be the last one in date/time.
>
> Thanks a lot in advance.
>
> John
>
SELECT Title, PublishingDate
FROM Books
WHERE PublishingDate = (SELECT MAX(PublishingDate)
FROM Books
WHERE YEAR(PublishingDate) = 2002)
--
Rune Bivrin
- OOP since 1989
- SQL Server since 1990
- VB since 1991
# 2 Re: SQL: Select last records of the year
Thanks, Rune, your SQL works for year 2002, as I can see. How can you modify
it so that it works regardless of the year?
John
Rune Bivrin <rune@bivrin.com> wrote:
>"John" <John.Guo@Ametek-Online.com> wrote in
>news:3f453f8d$1@tnews.web.dev-archive.com:
>
>>
>> Hi, would anybody help with this SQL question:
>>
>> Suppose there is a table with two fields for the name of the booksp
>> publisehd
>> and the date/time the books are published. Assume there are N ( N =
>> 20,
>> for example) books published over ten years period. So In certain
>> year(s), there are more than one book published. Further assume don't
>> know the range of the years. Can we construct an SQL to select only
>> one book from a year (if there is a book in that year, of course) and
>> if there are more than one books publishd in a year, then the SQL
>> should pick only the last book published in that year?
>>
>>
>> The problem is that the query depends on the "dynamic" properties
>> of the resulting records selected by SQL. That is, we don't know how
>> many
>>
>> books been published in one year (anywhere from 0 to some number). We
>> cannot use >=1990 or <= 2002, etc. Second, we not only select only
>> one, but it must be the last one in date/time.
>>
>> Thanks a lot in advance.
>>
>> John
>>
>
>SELECT Title, PublishingDate
>FROM Books
>WHERE PublishingDate = (SELECT MAX(PublishingDate)
> FROM Books
> WHERE YEAR(PublishingDate) = 2002)
>
>--
>Rune Bivrin
> - OOP since 1989
> - SQL Server since 1990
> - VB since 1991
John at 2007-11-11 23:51:35 >
# 3 Re: SQL: Select last records of the year
"John" <John.Guo@Ametek-Online.com> wrote in
news:3f463b87$1@tnews.web.dev-archive.com:
>
> Thanks, Rune, your SQL works for year 2002, as I can see. How can you
> modify it so that it works regardless of the year?
>
> John
Not sure I understand the problem, but I'm guessing you want the last book
in every year a book was published:
SELECT Title, PublishingDate
FROM Books
WHERE PublishingDate = (SELECT MAX(PublishingDate)
FROM Books
GROUP BY YEAR(PublishingDate))
--
Rune Bivrin
- OOP since 1989
- SQL Server since 1990
- VB since 1991
# 4 Re: SQL: Select last records of the year
Rune Bivrin <rune@bivrin.com> wrote:
>"John" <John.Guo@Ametek-Online.com> wrote in
>news:3f463b87$1@tnews.web.dev-archive.com:
>
>>
>> Thanks, Rune, your SQL works for year 2002, as I can see. How can you
>> modify it so that it works regardless of the year?
>>
>> John
>
>Not sure I understand the problem, but I'm guessing you want the last book
>in every year a book was published:
>
>SELECT Title, PublishingDate
>FROM Books
>WHERE PublishingDate = (SELECT MAX(PublishingDate)
> FROM Books
> GROUP BY YEAR(PublishingDate))
>
>
>--
>Rune Bivrin
> - OOP since 1989
> - SQL Server since 1990
> - VB since 1991
Rune,
Your statement about the poblem is correct. For example,
SELECT DISTINCT Year([PublishingDate]) AS Year
FROM Books
ORDER BY Year([PublishingDate]) DESC;
will select only one record/book per year (GROUP BY will work too). I like
your SQL above so that after the SQL, the title of the book is also known,
while in my SQL above, the book title cannot follow the word DISTINC, otherwise,
all the books will be selected. However, we I try your SQL, Access gave me
an error saying "At most one record can be selcted from the SQL" and then
failed.
Thanks,
John
John at 2007-11-11 23:53:33 >
