Get data from last to first with join and distinct rows

Question

With reference to my previous question

http://www.c-sharpcorner.com/Forums/get-sql-data-last-to-first

I want to use features like pagination in sql server (as offset and fetch does in my previous question)

I have 2 tables

1. Purchase Table (contains id (identity column), invoiceNo, Date, NetAmt, vendorID, vendorName) (m mentioning some columns, as there are more columns)

2. Vendor Table (id (identity column), vendorID, vendorName)

Want to get distinct invoiceNo from purchase table

data to be fetch based on vendorID

I wrote my basic query like (which works fine)

select distinct p.InvoiceNo, p.Date, p.NetAmt as Amount, vd.VendorName from Purchase p
inner join VendorDetails vd on p.VendorID = vd.VendorId

Now adding pagination like functionality (getting rows from last to first) using offset and fetch

(till now it works fine)

select p.InvoiceNo, p.Date, p.NetAmt as Amount, vd.VendorName from Purchase p
inner join VendorDetails vd on p.VendorID = vd.VendorId
order by p.Id desc offset 0 rows fetch next 10 rows only

But I want distinct p.InvoiceNo here with pagination functionality)

select distinct p.InvoiceNo, p.Date, p.NetAmt as Amount, vd.VendorName from Purchase p
inner join VendorDetails vd on p.VendorID = vd.VendorId
order by p.Id desc offset 0 rows fetch next 10 rows only

Now the error comes

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Please suggest something.

Tapan Patel · Answer

I think it is not about pagination your query error itself guides you to correct the query. You are doing Order by id where id is not there in select list. Remove id from order by clause or add id in select list and it should work.

Ramesh Barik · Answer

SELECT FinalRes.InvoiceNo FinalRes.VendorID FinalRes.InvoiceNo FinalRes.DATE FinalRes.Amount FinalRes.VendorName FROM ( SELECT Row_number() OVER ( ORDER BY p.DATE ) AS FinalRow row InvoiceNo p.VendorID p.InvoiceNo p.DATE Amount VendorName FROM ( SELECT Row_number() OVER ( PARTITION BY p.VendorID ORDER BY p.DATE ) AS row p.VendorID p.InvoiceNo p.DATE p.NetAmt AS Amount vd.VendorName FROM Purchase p INNER JOIN VendorDetails vd ON p.VendorID = vd.VendorId ) P WHERE p.row = 1 ) AS FinalRes where FinalRes between 1 and 100 -- For pagenation

Tapan Patel · Answer

Possible solution would be store data in memory with ID in select list either in terms of subquery or temp table but the issue is you cannot do Offset Fetch without order by clause and if you do order by then you always have to select that column in select list. Which is kind of never ending loop. :) What you can do is to select id in your original query but discard it while handing the data in code- if you are using it.Not a perfect solution but it may work.

Anoop Bansal · Answer

Thank you both for the reply but as I required to get the unique invoice no I cannot put id on the select statement because there are several invoice no in the table. Also there should be order by id as it helps me to get the data in decending order. Then what will be the query to get the desired output. Thanks Anoop

Ehsan Sajjad · Answer

You need to specify the columns in select on which you are ordering or alternative is to use column number as mentioned in the following thread: http://stackoverflow.com/questions/265628/order-by-items-must-appear-in-the-select-list-if-select-distinct-is-specified Hope it helps!