indofraiser indofraiser - 1 year ago 111
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 Source

Since MySQL doesn't support window functions like ROW_NUMBER() and DENSE_RANK() , try doing it with a join :

 From TblReference t 
 LEFT JOIN TblReference s
  ON(t.docurl = s.docurl and t.element = s.element and < and = 'XYZ')
 Where t.Property= 'XYZ' AND is null
ORDER BY t.OrderColumn

I don't know how you use this functions, but your problem was that you didn't use the PARTITION BY part :

Select  * 
            ROW_NUMBER() over (PARTITION BY docurl,element order by id desc) As rn 
     From TblReference
     Where Property= 'XYZ' ) t 
Where t.rn = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download