Get data from last to first with join and distinct rows

Anoop Bansal

9y1.1k

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ramesh Barik9y ago
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 Patel9y ago
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 Bansal9y ago
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
Tapan Patel9y ago
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.
Ehsan Sajjad9y ago
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!