CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Abhishek
Abhishek

Reputation: 329

TOP And DISTINCT in one query

How can i use top and distinct in one statement i am using this but not success . I dont know what i am doing wrong .

Below is my statement that i am using but giving me exception.

 SELECT  TOP 30 * from
 (            
  SELECT   DISTINCT       
  Bill.CLIENTID,Bill.TRANDATE,Bill.TRANTYPE,Bill.DUE,Bill.AMOUNT,Bill.paid,ORDE_.INVDATE ,Bill.TRANDESC,Bill.INVNUM     
  FROM BILLING Bill  LEFT OUTER JOIN  ORDE_  ON Orde_.INVNUM = Bill.INVNUM          
 WHERE  Bill.CLIENTID = 1047  )

Upvotes: 1

Views: 155

Answers (2)

Kaf
Kaf

Reputation: 33839

Missing table alias for the subquery may be the cause of your error. You don't need a subquery, you can use both Distinct and Top together.

Top(n) records depend on the order of the result set. So don't forget to explicitly Order your results.

SELECT DISTINCT TOP(30) Bill.CLIENTID,Bill.TRANDATE,Bill.TRANTYPE,Bill.DUE,Bill.AMOUNT,
                        Bill.paid,ORDE_.INVDATE ,Bill.TRANDESC,Bill.INVNUM     
FROM BILLING Bill  LEFT OUTER JOIN  ORDE_  ON Orde_.INVNUM = Bill.INVNUM          
WHERE  Bill.CLIENTID = 1047
ORDER BY ....

Upvotes: 4

Nagaraj S
Nagaraj S

Reputation: 13484

It may help

SELECT  TOP 30 * from
 (            
  SELECT   DISTINCT       
  Bill.CLIENTID,Bill.TRANDATE,Bill.TRANTYPE,Bill.DUE,Bill.AMOUNT,Bill.paid,ORDE_.INVDATE ,Bill.TRANDESC,Bill.INVNUM     
  FROM BILLING Bill  LEFT OUTER JOIN  ORDE_  ON Orde_.INVNUM = Bill.INVNUM          
 WHERE  Bill.CLIENTID = 1047  ) Alias

Upvotes: 1

Related Questions