SQL Query Distinct with Row_Number

sql query distinct with Row_Number

Use this:

SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM
    (SELECT DISTINCT id FROM table WHERE fid = 64) Base

and put the "output" of a query as the "input" of another.

Using CTE:

; WITH Base AS (
    SELECT DISTINCT id FROM table WHERE fid = 64
)

SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum FROM Base

The two queries should be equivalent.

Technically you could

SELECT DISTINCT id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) AS RowNum 
    FROM table
    WHERE fid = 64

but if you increase the number of DISTINCT fields, you have to put all these fields in the PARTITION BY, so for example

SELECT DISTINCT id, description,
    ROW_NUMBER() OVER (PARTITION BY id, description ORDER BY id) AS RowNum 
    FROM table
    WHERE fid = 64

I even hope you comprehend that you are going against standard naming conventions here, id should probably be a primary key, so unique by definition, so a DISTINCT would be useless on it, unless you coupled the query with some JOINs/UNION ALL...

DISTINCT with ROW_NUMBER()

Slap it in a CTE first:

with CTE as
(
SELECT DISTINCT 
        case when tbprofile =  '3' then  'Applicant TBI'
             when tbprofile = '4' then  'Prayas Center'
             end 'Applicant Type',
        REPLACE(ISNULL(DATEPART(yyyy,b.govtimevalid), '-'),0,'-') as 'Year',
        [tbpan] AS 'Applicant Id',
        ISNULL(a.PCId, '-') as 'PCId',
        UPPER(tbname) AS 'Name',
        UPPER(isnull(formstatus,'IN PROGRESS')) AS 'Form Status',UPPER(isnull(pmuapproval,'-')) AS 'PMU Status', 
        case when pmuapproval = 'valid'
        then isnull (convert(Varchar, pmutimevalid, 107),'-')
        else isnull (convert(Varchar, pmutimeinvalid, 107),'-')
        end 'PMUDateTime',
        UPPER(ISNULL(govapproval,'-')) AS 'PMC Status',
        case when govapproval = 'valid'
        then isnull (convert(Varchar, govtimevalid, 107),'-')
        else isnull( convert(Varchar, govtimeinvalid, 107),'-')
        end 'PMCDateTime',
        ISNULL(SanctionedAmount,'0') AS 'Sanctioned Amount',
        ISNULL((SanctionedAmount-BalDisbursed),'0') AS 'Total Disbursed',ISNULL(BalDisbursed,'0') AS 'Total Balance' 
        FROM tb_User a 
        LEFT OUTER JOIN applied b ON a.tbpan=b.tbid 
        LEFT OUTER JOIN tb_SanctionInfo c ON a.PCId = c.PCId 
        LEFT OUTER JOIN tb_DisbursedInfo d ON c.PCId = d.PCId WHERE tbprofile !='1' AND tbprofile !='2' 
)
select CTE.*, ROW_NUMBER() OVER (order by [Applicant ID]) AS 'Sr. No'
from CTE

Also, For SQL server, use [] for alias, not ''

SQL Server 2012 - Using ROW_NUMBER() on DISTINCT

Try the below one,

SELECT * ,ROW_NUMBER() OVER(ORDER BY SchedTimeIn) AS ROWNUM
FROM    (
    SELECT DISTINCT
    FORMAT(CAST(SchedTi AS DATETIME),'hh:mm tt') AS SchedTimeIn,
    FORMAT(CAST(SchedTO AS DATETIME),'hh:mm tt') AS SchedTimeOut
    FROM tblemployee_schedule
)   AS  D
ORDER BY ROWNUM

In your query ROW_NUMBER() produce a new sequence number for each records , so the DISTINCT key will not wok for the same, that's why you are getting too many redundant records. So you can use the ROW_NUMBER() in an outer query to overcome this.

Select distinct rows when using row numbers

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

distinct and row_number do not work together

SELECT
  buildingName,
  ROW_NUMBER() OVER(ORDER BY buildingName) AS RowNum
FROM cityStatus
GROUP BY buildingName

T-SQL SELECT DISTINCT & ROW_NUMBER() OVER Ordering Problem

Is the RowId value you're getting correct? Perhaps you just need an ORDER BY RowId clause on the outer query?

Performance when using distinct and row_number pagination

The problem is that in order to get distinct values, the database engine must run the fun(a) function on all the records being selected.

If you do the fun(a) only in the final select, the distinct should not effect it, so it should run only on the final 20 records.

I've changed the derived table you've used to another cte (but that's a personal preference - seems to me more tidy not to mix derived tables and ctes):

;WITH CTE1 AS (
    SELECT DISTINCT A,B AS alias,C
    FROM someTable
), 
CTE2 AS
(
    SELECT *, ROW_NUMBER() OVER(ORDER BY alias) As RowNum
    FROM CTE1
)

SELECT *, FUN(A)
FROM CTE2
WHERE RowNum >= 1 
AND RowNum < 20

Please note that since the fun function is not deterministic you might get results that are different from your original query - so before adapting this solution compare the results first.

row number and distinct together

Why MySQL doesn't like this query:

SELECT @n := @n + 1 AS n,  DISTINCT  name FROM table1

This query is not correct because SQL's DISTINCT query modifier applies to the whole row, not just to a single column or subset of columns. This is a common misunderstanding made by SQL programmers.

In other words, the result is reduced by DISTINCT only if all columns are the same as another row.

Syntactically, the DISTINCT keyword must follow SELECT. It's not correct to put it after other columns.

You could write the following:

SELECT DISTINCT @n := @n + 1 AS n, name FROM table1

But this query wouldn't get you what you wanted. It would apply the incrementing variable to every row of table1, and then apply DISTINCT to the whole result. Since every row is guaranteed to have a distinct value n, the DISTINCT would have no effect.

Other answers have described doing the DISTINCT inside a derived table (subquery), so that it forces the rows to be reduced based only on the distinct values of name, and then apply the incrementing variable to the resulting rows.

---

Related Topics