indofraiser indofraiser - 5 months ago 12
SQL Question

Select distinct rows when using row numbers

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'


My code is pulling up 34 and I've change Row_Number to DENSE_RANK but still not able to get down to 14;

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


*** the rn is in a variable loop to count up to 10 normally.

Reference: sql query distinct with Row_Number --

SELECT distinct id, DENSE_RANK() OVER (ORDER BY id) AS RowNum
FROM table
WHERE fid = 64

Answer

Since MySQL doesn't support window functions like ROW_NUMBER() and 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