Sorting "both" ways?
in [Server Configuration]
|
From: Maury Markowitz on 26 Jun 2008 18:07 Ok, bear with with me here... I have a big table (well, 500 rows) that has a list of debits, credits, and "other". I'd like to write a SQL statement to retrieve these, sorted in an odd way... 1) sort by group, so we get all the credits, then the debits, then the "other" 2) sort by magnitude, but not just ABS(x), for the credits it needs to have the "most positive" numbers at the top, the debits need to have the "most negative" at the top, and the "other" should have no sorting at all. Is there some way I could do this with a single statement? I tried... ORDER BY CrDb, SIGN(value) DESC, value DESC but that always sorts biggest to smallest for all the groups. What I'd like is something more like... ORDER BY CrDb, CASE CrDb WHEN 'c' THEN value DESC ELSE value END but any attempt to put in a CASE fails. I could make a new "psuedocolumn" in the SELECT part, but I don't want it to show up in the output. Any ideas? Maury
From: Aaron Bertrand [SQL Server MVP] on 26 Jun 2008 18:16 If you have one in asc and one in desc, you must separate them. Note that only one will really used depending on the other values in the row. ORDER BY CrDb, CASE CrDb WHEN 'c' THEN Value END DESC, CASE WHEN CrDb <> 'c' THEN Value END On 6/26/08 6:07 PM, in article 7f7dea1b-6d99-4041-81a8-4592b747631d(a)c65g2000hsa.googlegroups.com, "Maury Markowitz" <maury.markowitz(a)gmail.com> wrote: > Ok, bear with with me here... > > I have a big table (well, 500 rows) that has a list of debits, > credits, and "other". I'd like to write a SQL statement to retrieve > these, sorted in an odd way... > > 1) sort by group, so we get all the credits, then the debits, then the > "other" > 2) sort by magnitude, but not just ABS(x), for the credits it needs to > have the "most positive" numbers at the top, the debits need to have > the "most negative" at the top, and the "other" should have no sorting > at all. > > Is there some way I could do this with a single statement? I tried... > > ORDER BY CrDb, SIGN(value) DESC, value DESC > > but that always sorts biggest to smallest for all the groups. What I'd > like is something more like... > > ORDER BY CrDb, CASE CrDb WHEN 'c' THEN value DESC ELSE value END > > but any attempt to put in a CASE fails. I could make a new > "psuedocolumn" in the SELECT part, but I don't want it to show up in > the output. > > Any ideas? > > Maury
From: Sha Anand on 27 Jun 2008 05:00 ORDER BY GroupCode, CASE WHEN GroupCode = 'C' THEN 1 * Amt ELSE -1 * Amt END DESC - Sha Anand "Maury Markowitz" wrote: > Ok, bear with with me here... > > I have a big table (well, 500 rows) that has a list of debits, > credits, and "other". I'd like to write a SQL statement to retrieve > these, sorted in an odd way... > > 1) sort by group, so we get all the credits, then the debits, then the > "other" > 2) sort by magnitude, but not just ABS(x), for the credits it needs to > have the "most positive" numbers at the top, the debits need to have > the "most negative" at the top, and the "other" should have no sorting > at all. > > Is there some way I could do this with a single statement? I tried... > > ORDER BY CrDb, SIGN(value) DESC, value DESC > > but that always sorts biggest to smallest for all the groups. What I'd > like is something more like... > > ORDER BY CrDb, CASE CrDb WHEN 'c' THEN value DESC ELSE value END > > but any attempt to put in a CASE fails. I could make a new > "psuedocolumn" in the SELECT part, but I don't want it to show up in > the output. > > Any ideas? > > Maury >
|
Pages: 1 Prev: SQL Server 2008 Next: How to encrypt data so DBA can decrypt |