Aim: Get distinct values based on fields 'DOCURL' and 'ELEMENT'.
Notes: (editted) The below is to be used on the .aspx page and not in SQL
Issue: If I run as a standalone query I get 14 lines, which is correct;
Select DISTINCT DOCURL, ELEMENT From TblReference Where Property= 'XYZ'
Select * from( Select DISTINCT DOCURL, ELEMENT,
DENSE_RANK() over (order by id desc) As rn From TblReference
Where Property= 'XYZ' ) as t Where rn = 1
SELECT distinct id, DENSE_RANK() OVER (ORDER BY id) AS RowNum
WHERE fid = 64
Since MySQL doesn't support window functions like
DENSE_RANK() , try doing it with a join :
Select t.DOCURL, t.ELEMENT From TblReference t LEFT JOIN TblReference s ON(t.docurl = s.docurl and t.element = s.element and s.id < t.id and s.property = 'XYZ') Where t.Property= 'XYZ' AND s.id is null ORDER BY t.OrderColumn LIMIT 10;
I don't know how you use this functions, but your problem was that you didn't use the
PARTITION BY part :
Select * from(Select DISTINCT DOCURL, ELEMENT, ROW_NUMBER() over (PARTITION BY docurl,element order by id desc) As rn From TblReference Where Property= 'XYZ' ) t Where t.rn = 1