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

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)

 

Answer 1

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...

Answer 2

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

Answer 3

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)