locked
Sub Query(Select Top1 Order ByDESC) not selecting 1 but returning all records in that category RRS feed

  • Question

  • User-1215897877 posted

    I have written a query with the hopes of it returning records based on latest Date.

     

    In this particular query i used a select top 1 ,order by desc statement.However i dont get the top 1 date ,rather i get all dates :

    This is my query:

    SELECT Serial Number,CustomerName,CellphoneModel..........

    FROM CustomersTable INNER JOIN CellPhoneTables ON CustomersTable.CustomerID = CellphoneTable.CustomerID INNER JOIN MaintainanceTable......

                                    ....... AND CellphoneTables.SerialNumber IN

                                  (SELECT TOP(1) Serial Number

                                  FROM   CellPhoneTable

                                  WHERE (CustomerID = CustomersTable.CustomerID)

                                  ORDER BY MaintananceTable.CheckDate DESC)

     

    These are my results::

    Serial Number  Customers .............................. CheckedOnDate

    11111                HomeCell                           1/1/2012

    11111                HomeCell                           1/2/2012

    11111                HomeCell                           7/7/2012

    22222                BarbsConnect                     2/3/2012

    22222                Barbsconnect                      18/7/2012

    These are my desired results:

    SerialNumber    Customer                        CheckedOnDate

    11111              HomeCell                          7/72012

    22222              BarbsConnect                    18/7/2012

    I did a practice example below:(which worked) so what could i have done wrong on my my example above?

    SELECT        a.AuthorID, a.AuthorName, b.ISDN, b.BookTitle, b.NumberOfPages, b.DatePublished, b.Author

    FROM            Authors AS a LEFT OUTER JOIN                          Books AS b ON a.AuthorID = b.Author AND b.ISDN IN          

                        (SELECT        TOP (1) ISDN                            

                      FROM            Books                               

                 WHERE        (Author = a.AuthorID)                        

                ORDER BY DatePublished)

     

    Monday, October 14, 2013 3:27 AM

Answers

  • User-1215897877 posted

    I got a solution from another forum ,though i should share it:

     

    select  * from CustomersTable CT inner join CellphoneTables CellTbl on CT.CustomerID =    CellTbl.CustomerID inner join MaintainanceTable MT on MT.CustomerID = CT.CustomerID and MT.CheckDate in    

       (SELECT TOP(1) CheckDate                        

        FROM   MaintainanceTable        

                        WHERE (CustomerID = CT.CustomerID)          

                      ORDER BY MT.CheckDate desc)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2013 6:36 AM

All replies

  • User551462331 posted

    try this

    Select [Searial Number], CustomerName,... , max(CheckedOnDate) as CheckedOnDate
    (
    SELECT Serial Number,CustomerName,CellphoneModel..........
      FROM CustomersTable INNER JOIN CellPhoneTables ON CustomersTable.CustomerID = CellphoneTable.CustomerID INNER JOIN MaintainanceTable......
    )A
    group by [Searial Number], CustomerName... --all column names from select list except checkedondate

    hope this helps...

    Monday, October 14, 2013 3:36 AM
  • User-1215897877 posted

    I tried using that query using max like u mentioned above, however i get More records returned,with duplicates and not max date that i want

    Monday, October 14, 2013 4:01 AM
  • User-1215897877 posted

    I got a solution from another forum ,though i should share it:

     

    select  * from CustomersTable CT inner join CellphoneTables CellTbl on CT.CustomerID =    CellTbl.CustomerID inner join MaintainanceTable MT on MT.CustomerID = CT.CustomerID and MT.CheckDate in    

       (SELECT TOP(1) CheckDate                        

        FROM   MaintainanceTable        

                        WHERE (CustomerID = CT.CustomerID)          

                      ORDER BY MT.CheckDate desc)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 14, 2013 6:36 AM