Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Angry Unanswered: Getting a field added onto DISTINCT

    Hi. I've used MySQL before, but am new to T-SQL:

    I have a query where I need to return 5 rows. 4 of them are in an outer query and 1 of them (ImportDate) has unique entries per row:

    Code:
    SELECT CustomerNumber, PortfolioNo, ShortName, LongName, ImportDate
    FROM EQH1 eq
    WHERE ImportDate = 
    (SELECT DISTINCT ImportDate
    FROM EQH1
    WHERE ImportDate >= CONVERT(varchar(10), CONVERT(varchar(10), '2008-08-04 00:00:00', 103),121)) -- yyyy-mm-dd
    AND ImportDate <= CONVERT(datetime, CONVERT(datetime, '04/08/2008', 103),121)
    AND eq.PortfolioNo not in (99,98,97)
    I am able to return the import date without the DISTINCT clause. However, I only want to return the first entry of each CustomerNumber with matching portfolio number and then add the ImportDate back in.

    In between the outer query and the subquery, I have tried using =, returning the following error:
    "This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

    Also, I have tried replacing the = with IN, and returned no records.
    Finally, I tried a DISTINCT on the CustomerNumber and PortfolioNo, both of which returned no results.

    There are 2 records in my EQH1 table that have an import date of 04/08/2008

    Have I missed an important aspect in coding this query?
    Can what I have described earlier be achieved without a subquery?

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    seems to be possible .. can u make things a bit more clear by some sample data?

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mbewers980
    I only want to return the first entry of each CustomerNumber with matching portfolio number
    Write this part first as a single query.

    You can then use this query as a derived table and join back to it to retrieve the other columns.
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2008
    Posts
    4
    Not sure how you make a derived table? Do you mean a view:

    For the benefit of anyone else who has had this problem, here is my result using the IN clause to look for an import date and selecting TOP and ImportDte ASC so we always retrieve the earliest importDate:

    Code:
    SELECT CustomerNumber, PortfolioNo, ShortName, LongName, ImportDate
    FROM EQH1 eq
    WHERE ImportDate IN    (SELECT TOP 1 ImportDate 
    			FROM EQH1
    			WHERE ImportDate >= CONVERT(datetime, CONVERT(datetime, @CalendarFrom, 103),121) -- yyyy-mm-dd
    			AND ImportDate <= CONVERT(datetime, CONVERT(datetime, @CalendarTo, 103),121)
    			ORDER BY ImportDate DESC)
    
    AND NOT EXISTS  
    (select CustomerNumber, PortfolioNo, ShortName, LongName, ImportDate
    from eqh1 eq2
    where ImportDate < CONVERT(datetime, CONVERT(datetime, @CalendarFrom, 103),121)
    AND eq.PortfolioNo not in (99,98,97)
    AND eq.CustomerNumber = eq2.CustomerNumber
    AND eq.PortfolioNo = eq2.PortfolioNo)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A view can be considered a derived table, yes.

    However, you can achieve this "in line" using the following method
    Code:
    SELECT *
    FROM   (
        SELECT department
             , Count(*) As [total]
        FROM    employees
        GROUP
            BY department
        ) As [derived_table]
    Just taking a quick glance at your code, you can replace the TOP 1 with a Max()
    Code:
    WHERE  importdate = (SELECT Max(import_date) FROM ... WHERE ...)
    Also, why are you converting @calendarfrom and @calendarto twice?

    It's quite hard to see and understand what you're trying to achieve, so it might be worth looking back at post #2 and responding appropriately.
    I'm sure we can come up with a cleaner, simpler and more efficient solution to your problem after we understand it entirely
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •