Distinct question.

[jjjax64]

We are trying to return the last top 9 records for a specific customer but don't want duplicate dates. Problem is that we need to order descending on and id field so that makes it not distinct. Here is our current code.

SELECT Top 9
tblohb_Customer,
tblohb_InvoiceDate,
tblohb_Facility
FROM tbl_OrderHeaderBilled
WHERE tblohb_Facility = '01' AND
@CustNum = tblohb_Customer
ORDER BY tblohb_ID DESC


Thanks,
Joe

 

[markros]

In SQL Server 2005 and up:

;with cte as (select *, dense_rank() over (partition by Customer order by [DateFld] DESC) as Row)

select * from cte where Row <=9

Also, if possible, can you drop all these tblohb_ prefixes for the field names? They make the queries hard to write and complicate the picture. What is the purpose of the prefixes?

PluralSight Learning Library

 

[djj55]

It tells where the column came from. tblohb came from table (tbl) OrderHeaderBilled (ohb).

Granted the prefixes make the question hard to understand but everyone has their own methods for keeping things straight.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[markros]

BTW, I forgot the FROM clause in CTE.

PluralSight Learning Library