indofraiser - 9 months ago 45

SQL 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

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
```