Select distinct rows when using row numbers

Question

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

The code works spot on bar the removing distinct, in the .NET code where it usually is it starts... For i = 1 To 10 sSQL = "Select * from( Select id, DOCURL, VFMDISCIPLINEELEMENT, row_number() over (order by id desc) As rn From TblData Where UPRN = '" & Session.Item("pUPRN").ToString & "') as t Where rn = " & i & "" dsNames.SelectCommand = sSQL — indofraiser, Jun 21 '16 at 08:20

sagi · Accepted Answer · 2016-06-21T08:27:10.580

1

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

edited Jun 21 '16 at 08:27

answered Jun 21 '16 at 08:23

sagi

40,026
6
59
84

Thanks, I'd need to limit this to the first ten and please see amendments above, code will be on the .aspx page so I may have mislead on the initial post (notes added) – indofraiser Jun 21 '16 at 08:26
Sorry, I don't know PHP. What is wrong with this query? @indofraiser – sagi Jun 21 '16 at 08:28
Thanks, both useful. Turned out I has a loop in the wrong place earlier on essentially messing with the 'i' function as well, bit of a red herring! – indofraiser Jun 21 '16 at 09:02

Select distinct rows when using row numbers

1 Answers1