Home All Groups Group Topic Archive Search About

return results of lastest date

microsoft.public.sqlserver.programming
Author
29 Sep 2005 4:34 PM
gv
Hi all,

The following query returns the following results.

SELECT  docCategories.docCat, docNames.docName, docVersions.docVersionDate
FROM docCategories, docNames, docVersions
WHERE docNames.deletedFlag = 0 AND
           docNames.docCatID=docCategories.docCatID AND
           docNames.docNameID=docVersions.docNameID

ORDER BY docCategories.docCat, docNames.docName ASC

DC          Aquaman                      2005-08-15 00:00:00.000
DC          Batman                         2005-08-15 00:00:00.000
DC          Flash                            2005-09-13 00:00:00.000
DC          Flash                            2005-09-15 00:00:00.000
DC          Justice League              2005-11-30 00:00:00.000
DC          Superman                    1967-04-30 00:00:00.000
DC          Superman                     2005-06-15 00:00:00.000
DC          Superman                     2005-06-30 00:00:00.000
DC          Superman                     2005-07-13 00:00:00.000
Marvel     Amazing Spider-Man    2005-06-30 00:00:00.000
Marvel     Amazing Spider-Man    2005-08-15 00:00:00.000
Marvel     Amazing Spider-Man    2005-09-15 00:00:00.000
Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
Marvel     Avengers                      2005-06-30 00:00:00.000
Marvel     Fantastic Four               2005-04-23 00:00:00.000
Marvel     Incredible Hulk             2005-06-30 00:00:00.000
Marvel     Incredible Hulk             2005-08-15 00:00:00.000
Marvel     Punisher                        2005-08-15 00:00:00.000

I only want the latest date for each docname in docCat

So would return this:

DC          Aquaman                     2005-08-15 00:00:00.000
DC          Batman                         2005-08-15 00:00:00.000
DC          Flash                            2005-09-15 00:00:00.000
DC          Justice League              2005-11-30 00:00:00.000
DC          Superman                    2005-07-13 00:00:00.000
Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
Marvel     Avengers                      2005-06-30 00:00:00.000
Marvel     Fantastic Four               2005-04-23 00:00:00.000
Marvel     Incredible Hulk             2005-08-15 00:00:00.000
Marvel     Punisher                        2005-08-15 00:00:00.000

thanks for your help
gv

Author
29 Sep 2005 4:55 PM
Jerry Spivey
Try:

SELECT  docCategories.docCat, docNames.docName,
MAX(docVersions.docVersionDate)
FROM docCategories, docNames, docVersions
WHERE docNames.deletedFlag = 0 AND
           docNames.docCatID=docCategories.docCatID AND
           docNames.docNameID=docVersions.docNameID
GROUP BY docCategories.docCat, docNames.docName

HTH

Jerry

Show quote
"gv" <viat***@musc.edu> wrote in message
news:OdvVORRxFHA.3312@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> The following query returns the following results.
>
> SELECT  docCategories.docCat, docNames.docName, docVersions.docVersionDate
> FROM docCategories, docNames, docVersions
> WHERE docNames.deletedFlag = 0 AND
>           docNames.docCatID=docCategories.docCatID AND
>           docNames.docNameID=docVersions.docNameID
>
> ORDER BY docCategories.docCat, docNames.docName ASC
>
> DC          Aquaman                      2005-08-15 00:00:00.000
> DC          Batman                         2005-08-15 00:00:00.000
> DC          Flash                            2005-09-13 00:00:00.000
> DC          Flash                            2005-09-15 00:00:00.000
> DC          Justice League              2005-11-30 00:00:00.000
> DC          Superman                    1967-04-30 00:00:00.000
> DC          Superman                     2005-06-15 00:00:00.000
> DC          Superman                     2005-06-30 00:00:00.000
> DC          Superman                     2005-07-13 00:00:00.000
> Marvel     Amazing Spider-Man    2005-06-30 00:00:00.000
> Marvel     Amazing Spider-Man    2005-08-15 00:00:00.000
> Marvel     Amazing Spider-Man    2005-09-15 00:00:00.000
> Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
> Marvel     Avengers                      2005-06-30 00:00:00.000
> Marvel     Fantastic Four               2005-04-23 00:00:00.000
> Marvel     Incredible Hulk             2005-06-30 00:00:00.000
> Marvel     Incredible Hulk             2005-08-15 00:00:00.000
> Marvel     Punisher                        2005-08-15 00:00:00.000
>
> I only want the latest date for each docname in docCat
>
> So would return this:
>
> DC          Aquaman                     2005-08-15 00:00:00.000
> DC          Batman                         2005-08-15 00:00:00.000
> DC          Flash                            2005-09-15 00:00:00.000
> DC          Justice League              2005-11-30 00:00:00.000
> DC          Superman                    2005-07-13 00:00:00.000
> Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
> Marvel     Avengers                      2005-06-30 00:00:00.000
> Marvel     Fantastic Four               2005-04-23 00:00:00.000
> Marvel     Incredible Hulk             2005-08-15 00:00:00.000
> Marvel     Punisher                        2005-08-15 00:00:00.000
>
> thanks for your help
> gv
>
>
>
Author
29 Sep 2005 4:58 PM
gv
Thanks!! that works fine

gv



Show quote
"Jerry Spivey" <jspi***@vestas-awt.com> wrote in message
news:O2vsfaRxFHA.2924@TK2MSFTNGP15.phx.gbl...
> Try:
>
> SELECT  docCategories.docCat, docNames.docName,
> MAX(docVersions.docVersionDate)
> FROM docCategories, docNames, docVersions
> WHERE docNames.deletedFlag = 0 AND
>           docNames.docCatID=docCategories.docCatID AND
>           docNames.docNameID=docVersions.docNameID
> GROUP BY docCategories.docCat, docNames.docName
>
> HTH
>
> Jerry
>
> "gv" <viat***@musc.edu> wrote in message
> news:OdvVORRxFHA.3312@TK2MSFTNGP09.phx.gbl...
>> Hi all,
>>
>> The following query returns the following results.
>>
>> SELECT  docCategories.docCat, docNames.docName,
>> docVersions.docVersionDate
>> FROM docCategories, docNames, docVersions
>> WHERE docNames.deletedFlag = 0 AND
>>           docNames.docCatID=docCategories.docCatID AND
>>           docNames.docNameID=docVersions.docNameID
>>
>> ORDER BY docCategories.docCat, docNames.docName ASC
>>
>> DC          Aquaman                      2005-08-15 00:00:00.000
>> DC          Batman                         2005-08-15 00:00:00.000
>> DC          Flash                            2005-09-13 00:00:00.000
>> DC          Flash                            2005-09-15 00:00:00.000
>> DC          Justice League              2005-11-30 00:00:00.000
>> DC          Superman                    1967-04-30 00:00:00.000
>> DC          Superman                     2005-06-15 00:00:00.000
>> DC          Superman                     2005-06-30 00:00:00.000
>> DC          Superman                     2005-07-13 00:00:00.000
>> Marvel     Amazing Spider-Man    2005-06-30 00:00:00.000
>> Marvel     Amazing Spider-Man    2005-08-15 00:00:00.000
>> Marvel     Amazing Spider-Man    2005-09-15 00:00:00.000
>> Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
>> Marvel     Avengers                      2005-06-30 00:00:00.000
>> Marvel     Fantastic Four               2005-04-23 00:00:00.000
>> Marvel     Incredible Hulk             2005-06-30 00:00:00.000
>> Marvel     Incredible Hulk             2005-08-15 00:00:00.000
>> Marvel     Punisher                        2005-08-15 00:00:00.000
>>
>> I only want the latest date for each docname in docCat
>>
>> So would return this:
>>
>> DC          Aquaman                     2005-08-15 00:00:00.000
>> DC          Batman                         2005-08-15 00:00:00.000
>> DC          Flash                            2005-09-15 00:00:00.000
>> DC          Justice League              2005-11-30 00:00:00.000
>> DC          Superman                    2005-07-13 00:00:00.000
>> Marvel     Amazing Spider-Man    2005-09-21 00:00:00.000
>> Marvel     Avengers                      2005-06-30 00:00:00.000
>> Marvel     Fantastic Four               2005-04-23 00:00:00.000
>> Marvel     Incredible Hulk             2005-08-15 00:00:00.000
>> Marvel     Punisher                        2005-08-15 00:00:00.000
>>
>> thanks for your help
>> gv
>>
>>
>>
>
>

AddThis Social Bookmark Button

Post Other interesting topics
adding 140,000 records requires index rebuild. make sense?
Need advice with a query
Index Tuning Wizard
Update Trigger
Return Variable In Resultset!