order by values in IN clause

Hi All,
I have a problem [stated below].
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('2001','23456789') GROUP BY TS
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
both return same result set.
$99,200.00
$4,343.00
I need to order the result set by the values that I give in the IN
clause. Is this possible?Anybody could please explain why this is
happening and what would be the remedy.
Thanks in advance.
Thanks & Regards,
Shankar.> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
SQL Server is free to return results in any order unless you specify ORDER
BY. This is regardless of the order of values in your IN clause, order of
joined tables, table indexes, etc.
One solution is to add an additional value for the desired sequencing so
that you can specify ORDER BY. The example below uses a derived table:
SELECT '$'+CONVERT(VARCHAR,SUM(mMoney),1)
FROM trans
JOIN (SELECT '2001' AS TS, 1 AS Seq
UNION ALL SELECT '23456789', 2) AS trans_list
ON trans.TS = trans_list.TS
GROUP BY trans_list.TS, trans_list.Seq
ORDER BY trans_list.Seq
BTW, formatting data for display purposes is a job best done in the
presentation layer rather than in the database. That approach is more
scalable. Also, front-end tools (e.g. Reporting Services) provide much
richer formatting capability than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<shankararaman.s@.gmail.com> wrote in message
news:1146824029.416992.301160@.j73g2000cwa.googlegroups.com...
> Hi All,
> I have a problem [stated below].
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('2001','23456789') GROUP BY TS
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('23456789','2001') GROUP BY TS
> both return same result set.
> $99,200.00
> $4,343.00
> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
>
> Thanks in advance.
> Thanks & Regards,
> Shankar.
>|||try this.
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
order by TS|||Also format the data in your front end application
Madhivanansql