CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Nourhene
Nourhene

Reputation: 75

DISTINCT is not working as needed

I have a query and I need to show data with distinct id 

 Query= "SELECT * FROM (" &_

 " SELECT DISTINCT t.TICKET_ID as TICKET_ID, "&_
 " ROW_NUMBER() OVER (ORDER BY t.TICKET_ID DESC)NUM,"&_
" t.SHORT_DESCRIPTION as SHORT_DESCRIPTION, "&_
" p.TITLE as product_name"&_
" FROM T_TICKET as t,T_USER_PRODUCT as up "&_
" ,T_USER as u, T_PRODUCT as p" & _
 pReleaseFrom & _
" WHERE u.USER_ID=t.EDITOR_ID  AND not t.SHORT_DESCRIPTION is null" & _
 StateCondition & _

" ) A"& _   
" WHERE NUM BETWEEN " & Session("Start") & " AND " & Session("Endi")
Query=Query & " ORDER BY "&orderBy

but some rows are not distinct , and it 's not even making the order by t.TICKET_ID DESC.

Any help please ?

Upvotes: 0

Views: 81

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

One problem is in:

ROW_NUMBER() OVER (ORDER BY t.TICKET_ID DESC) NUM

When you select all rownumbers, they are numbers like 1,2,3,4,5 and by design they are distinct. So it is no point to SELECT DISTINCT from distinct row numbers. Suppose that TICKET_ID is a unique primary key.

Another thing is that you possibly have a problem in the part:

FROM T_TICKET as t, T_USER_PRODUCT as up ,T_USER as u, T_PRODUCT as p

There you are actually doing CROSS JOIN- I am not sure if it is by design.

Also generating SQL command the way you do it is vulnerable to SQL injection attack. You should use parametrized query instead.

Upvotes: 2

TT.
TT.

Reputation: 16146

"The DISTINCT keyword eliminates duplicate rows from the results of a SELECT statement". This means that if you do a SELECT DISTINCT, no duplicate rows will appear in your resultset. The row is viewed in its entirety to determine whether rows are distinct, not to one specific column in a row as you seem to think.

Upvotes: 0

Related Questions