Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

DEMO

 SELECT DISTINCT
  P_Master.P_Name,ROW_NUMBER() OVER 
    (ORDER BY Visit_Date DESC,P_Master.P_Name ASC) AS RowNumber,
  (SELECT MAX(Visit_Date) FROM P_Visit v WHERE v.PID = p_Master.PID) as Visit_Date
  FROM P_Master
  LEFT JOIN P_VISIT
  ON P_Master.PID=P_VISIT.PID
  WHERE P_Master.P_Name LIKE 'j%'

I have to find out distinct records for given search criteria.

share|improve this question

closed as unclear what you're asking by billinkc, Max Vernon, Kin, RolandoMySQLDBA, Mikael Eriksson Nov 12 '13 at 20:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

    
What result do you expect? Do you actually care about having the row number in the output? Why? –  Aaron Bertrand Nov 12 '13 at 13:20
    
You need to add more detail to your question. –  Max Vernon Nov 12 '13 at 15:44

2 Answers 2

Here's a few ways of doing the same, you should test to see which is more effective.

-- Sub-query with GROUP BY
/* Note, left joining P_VISIT again here after SRC is not really necessary, since the date you're using is already contained within the sub query. But it's included for an example's sake.
*/ 
SELECT DENSE_RANK() OVER (ORDER BY v.Visit_Date DESC, m.P_Name ASC) RowNumber, m.P_Name, v.Visit_Date
FROM P_Master m
LEFT JOIN 
  (SELECT PID, MAX(Visit_Date) mxDate
  FROM P_VISIT
  GROUP BY PID) SRC ON SRC.PID = m.PID
LEFT JOIN P_VISIT v ON v.PID = SRC.PID AND v.Visit_Date = SRC.mxDate
WHERE m.P_Name LIKE 'j%'

-- OR OUTER APPLY
SELECT DENSE_RANK() OVER (ORDER BY SRC.Visit_Date DESC, m.P_Name ASC) RowNumber
  , m.P_Name, SRC.Visit_Date
FROM P_Master m
OUTER APPLY (SELECT TOP 1 * FROM P_VISIT v WHERE v.PID = m.PID ORDER BY v.Visit_Date DESC) SRC

-- OR an example that's as close as possible to your original query:
SELECT ROW_NUMBER() OVER (ORDER BY Visit_Date DESC, P_Name ASC) AS RowNumber, *
FROM
  (SELECT M.P_Name, MAX(V.Visit_Date) Visit_Date
  FROM P_Master M
  LEFT JOIN P_VISIT V ON M.PID=V.PID
  WHERE M.P_Name LIKE 'j%'
  GROUP BY M.P_Name) SRC

Both produce a unique (DISTINCT) result as long as the values in P_MASTER remain distinct, which by the looks of your example will be the case.

I would wager that the issue with your query is the way you run a sub query within the select to fetch the max value, which basically means it's being run against each non-distinct result set from outside of the sub query. But I'm pressed for time to check into that further.

These two will produce what you're looking for though. Just test which one is faster. :)

EDIT:

As per the discussion below, here's a version where you may have any, all or no filters and the query will still give the result based on what it can. Note, you'll have to replace the parameters in the code. And performance-wise, it might be a better idea to use several variations of the same query to match each set of search parameters, instead of trying to mash it all into one. But there it is. :)

[Revised SQLFiddle example:]

 DECLARE @PID INT, @PNAME VARCHAR(256), @VISITDATE DATE

--SELECT --@PID = 1, @visitdate = '2013-10-29', @PNAME = 'JOHN'
SET @PNAME = 'J'

SELECT ROW_NUMBER() OVER (ORDER BY Visit_Date DESC, P_Name ASC) AS RowNumber, *
FROM
  (SELECT SRC.P_Name, MAX(V.Visit_Date) Visit_Date
  FROM 
    (SELECT DISTINCT M.P_Name, M.PID 
    FROM P_Master M 
    LEFT JOIN P_VISIT V ON M.PID = V.PID
    WHERE (M.P_Name LIKE '%'+@PNAME+'%' OR @PNAME IS NULL)
    AND (M.PID = @PID OR @PID IS NULL)
    AND (CAST(V.Visit_Date AS DATE) = @VISITDATE OR @VISITDATE IS NULL)) SRC
  LEFT JOIN P_VISIT V ON SRC.PID=V.PID
  GROUP BY SRC.P_Name) SRC

EDIT 2:

See this link to join the full P_Master table to the result, so you can pick whichever columns you want from it. SQL Fiddle 2

share|improve this answer
    
Thanks for your reply kahn.. –  Nikhil Nov 12 '13 at 7:23
    
I have to show max date of column Visit_Date if i search on '2013-10-29' then record shows those customers have date '2013-10-29' in coloumn i have to shown max date of customer..this condition fails kahn –  Nikhil Nov 12 '13 at 7:42
    
Also added an alteration of your original query as third to the example list. –  Kahn Nov 12 '13 at 7:44
    
@Nikhil: Can you then please include the full list of all the potential filters. Let me check if I understand correctly: Currently you want a query that can be filtered either by name OR date, and whichever it is, you want to find the latest entry for the name associated with that filter? (IE, if the filter is a name, you want the latest entry for the name. If it's a date, you want all the latest entries of the names associated with that date?) –  Kahn Nov 12 '13 at 7:46
4  
That's why I've asked for clarification on the question. Shifting requirements and chameleon questions are no fun. –  Aaron Bertrand Nov 12 '13 at 14:43

Sounds like you just tried to make the query a lot more complex than it had to be. If you just want the last visit date for each distinct user:

;WITH x AS
(
    SELECT m.P_Name, m.PID, v.Visit_Date,
      rn = ROW_NUMBER() OVER (PARTITION BY m.P_Name ORDER BY v.Visit_Date DESC)
    FROM dbo.P_Master AS m
    LEFT OUTER JOIN dbo.P_Visit AS v
    ON m.PID = v.PID
    WHERE m.P_Name LIKE 'J%'
)
SELECT P_Name, PID, Visit_date FROM x WHERE rn = 1;

SQLFiddle demo

Some notes:

share|improve this answer
1  
Powerful Jedi is he, hehe –  swasheck Nov 12 '13 at 14:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.