May 22, 2012 at 10:27 am
I have a table having bigint stored in it.
CREATE TABLE #Number(Num BIGINT)
GO
INSERT INTO #Number(Num)
SELECT 2456 UNION ALL
SELECT 12343 UNION ALL
SELECT 445599995544 UNION ALL
SELECT 34534526262625263
SELECT * FROM #Number
I want out put to be ascending.
So, the result should be:
2456
12334
444455559999
22222333445556666
Any clue on how to get this done.
Thanks
May 22, 2012 at 10:30 am
SELECT *
FROM #Number
ORDER BY Num ASC
Just add teh ORDER BY clause..
May 22, 2012 at 10:37 am
ColdCoffee (5/22/2012)
SELECT *FROM #Number
ORDER BY Num ASC
Just add teh ORDER BY clause..
No, I am not looking for this.
I want the numbers with a columns shifted according to the order.
For Ex:
4321 should be displayed as 1234
May 22, 2012 at 10:43 am
Confusing Queries (5/22/2012)
No, I am not looking for this.I want the numbers with a columns shifted according to the order.
Can you elaborate on the problem? I am really confused here
Confusing Queries (5/22/2012)
For Ex:
4321 should be displayed as 1234
CONVERT to VARCHAR and then use REVERSE.
If you want a sturdy response, please provide more insight on to the problem, please.
May 22, 2012 at 10:52 am
Bit of a guess: -
SELECT Num,
((SELECT bits
FROM (SELECT SUBSTRING(CAST(Num AS VARCHAR(19)),n,1),
Num
FROM #Number a
CROSS APPLY (SELECT *
FROM (VALUES(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),
(16),(17),(18),(19))a(n)
) b
WHERE SUBSTRING(CAST(Num AS VARCHAR(19)),n,1) <> ''
) a(bits,Num)
WHERE Num = tbl.Num
ORDER BY Num, bits
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS newNum
FROM #Number tbl;
Returns: -
Num newNum
-------------------- -------------------
2456 2456
12343 12334
445599995544 444455559999
34534526262625263 22222333445556666
May 22, 2012 at 11:00 am
Cadavre (5/22/2012)
Bit of a guess: -
SELECT Num,((SELECT bits
FROM (SELECT SUBSTRING(CAST(Num AS VARCHAR(19)),n,1),
Num
FROM #Number a
CROSS APPLY (SELECT *
FROM (VALUES(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),
(16),(17),(18),(19))a(n)
) b
WHERE SUBSTRING(CAST(Num AS VARCHAR(19)),n,1) <> ''
) a(bits,Num)
WHERE Num = tbl.Num
ORDER BY Num, bits
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS newNum
FROM #Number tbl;
Returns: -
Num newNum-------------------- -------------------
2456 2456
12343 12334
445599995544 444455559999
34534526262625263 22222333445556666
Very nice! Now the question plaguing me is... Why on earth would we want to do this?!
Jared
CE - Microsoft
May 22, 2012 at 11:02 am
SQLKnowItAll (5/22/2012)
Very nice! Now the question plaguing me is... Why on earth would we want to do this?!
The only reason that springs to mind is interview/homework question to see if you can come up with a creative solution. . . which suggests I should probably remove my answer 😉
May 22, 2012 at 11:08 am
SQLKnowItAll (5/22/2012)
Very nice! Now the question plaguing me is... Why on earth would we want to do this?!
To implement Kaprekar Series...
May 22, 2012 at 11:09 am
Cadavre (5/22/2012)
Bit of a guess: -
SELECT Num,((SELECT bits
FROM (SELECT SUBSTRING(CAST(Num AS VARCHAR(19)),n,1),
Num
FROM #Number a
CROSS APPLY (SELECT *
FROM (VALUES(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),
(16),(17),(18),(19))a(n)
) b
WHERE SUBSTRING(CAST(Num AS VARCHAR(19)),n,1) <> ''
) a(bits,Num)
WHERE Num = tbl.Num
ORDER BY Num, bits
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS newNum
FROM #Number tbl;
Returns: -
Num newNum-------------------- -------------------
2456 2456
12343 12334
445599995544 444455559999
34534526262625263 22222333445556666
Houston, we have a problem. Try inserting a duplicate into the #number table...
Jared
CE - Microsoft
May 22, 2012 at 11:54 am
Confusing Queries (5/22/2012)
SQLKnowItAll (5/22/2012)
Very nice! Now the question plaguing me is... Why on earth would we want to do this?!To implement Kaprekar Series...
With the amount of looping and such I think you will find the performance is pretty bad when compared to doing this in a programming language.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2012 at 12:13 pm
SQLKnowItAll (5/22/2012)
Cadavre (5/22/2012)
Bit of a guess: -
SELECT Num,((SELECT bits
FROM (SELECT SUBSTRING(CAST(Num AS VARCHAR(19)),n,1),
Num
FROM #Number a
CROSS APPLY (SELECT *
FROM (VALUES(1),(2),(3),(4),(5),
(6),(7),(8),(9),(10),
(11),(12),(13),(14),(15),
(16),(17),(18),(19))a(n)
) b
WHERE SUBSTRING(CAST(Num AS VARCHAR(19)),n,1) <> ''
) a(bits,Num)
WHERE Num = tbl.Num
ORDER BY Num, bits
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS newNum
FROM #Number tbl;
Returns: -
Num newNum-------------------- -------------------
2456 2456
12343 12334
445599995544 444455559999
34534526262625263 22222333445556666
Houston, we have a problem. Try inserting a duplicate into the #number table...
Most definitely. What we needed was a primary key for the WHERE clause "WHERE Num = tbl.Num", but since there wasn't one I had to assume that the numbers are always unique.
May 22, 2012 at 1:29 pm
I would probably implement this in a SQLCLR scalar function, but this is another option for T-SQL:
SELECT
REPLICATE('0', LEN(f.string) - LEN(REPLACE(f.string, '0', ''))) +
REPLICATE('1', LEN(f.string) - LEN(REPLACE(f.string, '1', ''))) +
REPLICATE('2', LEN(f.string) - LEN(REPLACE(f.string, '2', ''))) +
REPLICATE('3', LEN(f.string) - LEN(REPLACE(f.string, '3', ''))) +
REPLICATE('4', LEN(f.string) - LEN(REPLACE(f.string, '4', ''))) +
REPLICATE('5', LEN(f.string) - LEN(REPLACE(f.string, '5', ''))) +
REPLICATE('6', LEN(f.string) - LEN(REPLACE(f.string, '6', ''))) +
REPLICATE('7', LEN(f.string) - LEN(REPLACE(f.string, '7', ''))) +
REPLICATE('8', LEN(f.string) - LEN(REPLACE(f.string, '8', ''))) +
REPLICATE('9', LEN(f.string) - LEN(REPLACE(f.string, '9', '')))
FROM #Number AS n
OUTER APPLY
(
SELECT
CONVERT(varchar(19),n.Num) COLLATE Latin1_General_BIN2
) AS f (string);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 1:45 pm
I still say the Kaprekar series is entirely too much looping for t-sql. It can be done but you will basically have to add some form of recursion to call the solution Paul suggested an indefinite number of times. The solution provided will return one set of number for the sequence but the recursion will be really bad performance wise on a lot of values. As a learning project this could be pretty cool but as far as a practical application of generating kaprekar series, I don't think t-sql is a good approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2012 at 3:25 pm
Sean Lange (5/22/2012)
I still say the Kaprekar series is entirely too much looping for t-sql. It can be done but you will basically have to add some form of recursion to call the solution Paul suggested an indefinite number of times. The solution provided will return one set of number for the sequence but the recursion will be really bad performance wise on a lot of values. As a learning project this could be pretty cool but as far as a practical application of generating kaprekar series, I don't think t-sql is a good approach.
You can find the winners of the Kaprekar T-SQL challenge here:
http://beyondrelational.com/puzzles/tsql/challenges/68/winners.aspx
http://beyondrelational.com/puzzles/tsql/challenges/68/performance-testing-comparision.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 22, 2012 at 3:38 pm
SQL Kiwi (5/22/2012)
Sean Lange (5/22/2012)
I still say the Kaprekar series is entirely too much looping for t-sql. It can be done but you will basically have to add some form of recursion to call the solution Paul suggested an indefinite number of times. The solution provided will return one set of number for the sequence but the recursion will be really bad performance wise on a lot of values. As a learning project this could be pretty cool but as far as a practical application of generating kaprekar series, I don't think t-sql is a good approach.You can find the winners of the Kaprekar T-SQL challenge here:
http://beyondrelational.com/puzzles/tsql/challenges/68/winners.aspx
http://beyondrelational.com/puzzles/tsql/challenges/68/performance-testing-comparision.aspx
I will ammend my statement...
I don't think t-sql is a good approach to solve for Keprekar series for mere mortals like myself. However, there are probably some ways to do this that are far more complicated than I would try to do with sql. I stand corrected, it can be done, even relatively quickly. If I had to write this I would still do it in a programming language. 😛
Thanks for sharing Paul. Those solutions are extremely inventive.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply