March 26, 2009 at 12:05 pm
hi
how can i show data in query for sql 2000 even and odd both.
suppose i have data in table --
1
2
3
4
5
6
7
8
.
.
.
thus millions
now i data to show like this --
2
1
4
3
6
5
8
7
.
.
.
thus millions
using CAST i can get odd or even but how should i get both
in this order--
2
1
4
3
6
5
8
7
.
.
.
thus millions
thank you neal
March 26, 2009 at 12:11 pm
Try this:
select number
from dbo.Numbers
where number between 1 and 10
order by floor((number+1)/2.0), number%2, number;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 1:59 pm
Here's a slightly different alternative that works for positive integers and is probably marginally faster. It doesn't work for negative integers.
SELECT number
FROM dbo.Numbers
WHERE number between 1 and 10
ORDER BY (number - 1) / 2, number & 1
March 26, 2009 at 2:06 pm
Just ran some tests. They have about the same execution time. Tried both with integers from -50,000 to +50,000, including 0. Both worked for the whole range.
Why would that one not work for negative numbers? It's just a bitwise operation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 2:34 pm
Both queries complete without an error, but negative numbers are returned in normal order.
However, it's probably just an academic point, and not relevant to the OP.
[font="Courier New"]-10
-9
-8
-7
-6
-5
-4
-3
-2
-1
0
2
1
4
3
6
5
8
7
10
9[/font]
March 26, 2009 at 2:39 pm
Yes, negative numbers appear in the "usual" order. In this case, that's even-odd, even-odd, pairs, just like the OP asked for. -10, -9, that's an even-odd pair. -8,-7, same thing.
Unless, of course, below zero, it should be -9,-10, which would be the opposite of the stated intent.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 3:26 pm
thanks for you reply how about if i dont want to limit it 1 to 10...... if i want all records in table to show like this... how should i do it then
~~neal~~
March 26, 2009 at 3:54 pm
In our example queries it is only really the ORDER BY clause that is relevant to your requirement. Since you didn't provide the structure of your table, we used a Numbers table to demonstrate the principle. If your table were named MyTable and your numeric column named MyColumn, then the following queries will not restrict the number of rows returned.
SELECT MyColumn
FROM MyTable
order by floor((MyColumn+1)/2.0), MyColumn%2, MyColumn;
SELECT MyColumn
FROM MyTable
ORDER BY (MyColumn - 1) / 2, MyColumn & 1
March 26, 2009 at 4:23 pm
In the spirit of the completely ridiculous, here goes:
SELECT id, CASE WHEN Abs( Sin(Ascii(Reverse (id) ) *
355.0 / 226)) < 7e-6 THEN 'Even' ELSE 'Odd' END type FROM
(SELECT t1 + t2 + t3 + t4 AS id
FROM (SELECT 0 AS t1 UNION SELECT 1
UNION SELECT 2) AS z1 CROSS JOIN (SELECT 0 AS t2
UNION SELECT 3 UNION SELECT 6) AS z2 CROSS
JOIN (SELECT 0 AS t3 UNION SELECT 9 UNION
SELECT 18) AS z3 CROSS JOIN (SELECT
0 AS t4 UNION SELECT 27 UNION SELECT 54) AS z4
)AS z ORDER BY (id+1) /2,type--***** ** ****
Results:
id type
----------- ----
0 Even
2 Even
1 Odd
4 Even
3 Odd
6 Even
5 Odd
8 Even
7 Odd
10 Even
9 Odd
12 Even
11 Odd
14 Even
13 Odd
16 Even
15 Odd
18 Even
17 Odd
20 Even
19 Odd
... and so on ...
We had a lot of fun with Even and Odd on this thread:
March 27, 2009 at 9:55 am
I'm a littel disappointed that no one wanted to play.
March 27, 2009 at 12:16 pm
HOW ABT INSTEAD FINDING EVEN AND ODD IF I WANT SPLIT THE TABLE AND SHOW LIKE THIS
1 6
2 7
3 8
4 9
5 10
March 27, 2009 at 12:17 pm
Split based on what criteria?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 27, 2009 at 3:04 pm
Michael Valentine Jones (3/26/2009)
In the spirit of the completely ridiculous, here goes:
Michael you may not have found a par playmate on this one but here's some sealion clapping for originality and effort, blimey, you guys make me feel like such a novice! Fantastic.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 27, 2009 at 3:16 pm
Michael Valentine Jones (3/27/2009)
I'm a littel disappointed that no one wanted to play.
Hi Michael
Not any reason. I also noticed the script and I have to say: "Respect for the creativity and the work!"
Nevertheless I have also to say, if you coding always in this format: "Thanks good that you are not in my team!" 😀
Greets
Flo
March 27, 2009 at 4:25 pm
I'm a littel disappointed that no one wanted to play.
I think this is in the spirit of that thread. I'm quite pleased to have found a new use for the SOUNDEX function, and the ORDER BY clause uses a single expression to corral the rows in the order required by the OP.
SELECT T.N, Parity = LEFT(SOUNDEX(CHAR(69 + 10 * (T.N % 2))), 1)
FROM Tally T WHERE T.N BETWEEN 1 AND 10
ORDER BY T.N - COS(PI() * T.N)
[font="Courier New"]N Parity
2 E
1 O
4 E
3 O
6 E
5 O
8 E
7 O
10 E
9 O[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply