Showing posts with label statementto. Show all posts
Showing posts with label statementto. Show all posts

Sunday, March 25, 2012

DISTINCT not returning data in sorted order after specific no. of

Hi
I have the problem with the DISTINCT keyword. I used the following statement
to get the distinct values from the table:
SELECT DISTINCT trade_name FROM Customer
This works absolutely perfect getting the distinct values and getting in
sorted order. But my requirement needs the statement to be as :
SELECT DISTINCT RTRIM(trade_name) FROM Customer
With the above statement, it works fine only if the table contains few 100
of records. My table contains around 6000+ records and the above statement
returns DISTINCTINCT values but does not get them in sorted order.
The same statement works fine if the there are around say 500+ records. I
see that the results displayed are distinct and sorted as well.
The field in question (trade_name) of 30 chars (char(30))
Why is this happening.Without ORDER BY the sort order is undefined for any SQL query. Add
ORDER BY:
SELECT DISTINCT trade_name
FROM Customer
ORDER BY trade_name
SELECT DISTINCT RTRIM(trade_name)
FROM Customer
ORDER BY RTRIM(trade_name)
David Portas
SQL Server MVP
--