May 31, 2010 at 12:50 am
i have a table where there is a column called priority(integer field)......i want distinct priority values with a 'ALL' ..to populate a dropdownlist
i have written a query like following:
select 'All' as priority from acchead union
select distinct convert(varchar(10),priority) priority from acchead
it gives me dataset like :
1
2
3
ALL
I want:
ALL
1
2
3
why the 'ALL' is coming at the end of the list??
May 31, 2010 at 4:04 am
If you want data ordered in a particular way, add ORDER BY to your query.
UNION doesn't enforce a particular order in the concatenated sets. Indeed, UNIONs are performed internally as a "distinc sort" operation, that means that your data is sorted to ensure that distinct results are returned.
You could try using UNION ALL, that doesn't perform the distinct calculation, but, again, to enforce a particular order use ORDER BY.
It could be something like this:
SELECT *
FROM (
SELECT 'ALL' AS Value
UNION ALL
SELECT DISTINCT CAST(Priority AS varchar(10))
FROM AccHead
) AS Data
ORDER BY CASE Value WHEN 'ALL' THEN 0 ELSE Value END
-- Gianluca Sartori
May 31, 2010 at 9:49 am
If you are using this for a drop-down, I would recommend having two columns. The first column will be the item value, the second column will be the item data.
SELECT 0 AS ItemValue, CAST('ALL' AS varchar(10)) As ItemData
UNION ALL
SELECT DISTINCT Priority, CAST(Priority AS varchar(10)) FROM acchead
With this, you use the item value column to sort the list in the drop-down and display the item data to the user for selection.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 31, 2010 at 9:04 pm
many many thanks jeffrey and gianluca............
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply