December 17, 2012 at 7:54 pm
I have table CREATE TABLE #varchar_field1 ( ID INT IDENTITY(1,1), mixed_field VARCHAR(100), )
INSERT INTO #varchar_field1 (mixed_field) SELECT '1' UNION ALL SELECT '4.9' UNION ALL SELECT '10'
UNION ALL SELECT '50' UNION ALL SELECT '6' UNION ALL SELECT 'a'
UNION ALL SELECT 'z' UNION ALL SELECT 'A'
--------output----
1 1
2 4.9
5 6
3 10
4 50
6 a
8 A
7 z
Result should be
IDmixed_field
11
24.9
310
450
56
6a
7z
8A
How can I get results like that. Please anyone help me to retrieve result.Thank you
December 17, 2012 at 8:55 pm
There is not guarantee to order with a union or selecting from a heap.
Use separate inserts to make sure order is preserved as below.
-- Create table
CREATE TABLE #varchar_field1
(
ID INT IDENTITY(1,1),
mixed_field VARCHAR(100)
);
GO
-- Insert in order (old way)
INSERT INTO #varchar_field1 (mixed_field) values ('1');
INSERT INTO #varchar_field1 (mixed_field) values ('14.9');
INSERT INTO #varchar_field1 (mixed_field) values ('10');
INSERT INTO #varchar_field1 (mixed_field) values ('50');
INSERT INTO #varchar_field1 (mixed_field) values ('6');
INSERT INTO #varchar_field1 (mixed_field) values ('a');
INSERT INTO #varchar_field1 (mixed_field) values ('z');
INSERT INTO #varchar_field1 (mixed_field) values ('A');
GO
-- Truncate table to reset id
truncate table #varchar_field1;
GO
-- New syntax for tuples (should preserve order like above)
INSERT INTO #varchar_field1 (mixed_field) values
('1'),
('14.9'),
('10'),
('50'),
('6'),
('a'),
('z'),
('A');
Please check the TSQL syntax since I did not execute in SSMS.
John Miner
Crafty DBA
www.craftydba.com
December 17, 2012 at 9:58 pm
Hi Miner,
I already made #table and I got result IDmixed_field
11
24.9
310
450
56
6a
7z
8A
Thank you for your Answer but I need Result below:
ID Mixed_field
1 1
2 4.9
5 6
3 10
4 50
6 a
8 A
7 z
December 17, 2012 at 11:04 pm
Not very elegant but you might be able to use something like this, assuming your intent is to sort numbers first as numbers followed by non-numeric characters:
CREATE TABLE #varchar_field1 ( ID INT IDENTITY(1,1), mixed_field VARCHAR(100) )
INSERT INTO #varchar_field1 (mixed_field)
SELECT '1' UNION ALL SELECT '4.9' UNION ALL SELECT '10'
UNION ALL SELECT '50' UNION ALL SELECT '6' UNION ALL SELECT 'a'
UNION ALL SELECT 'z' UNION ALL SELECT 'A'
SELECT *
FROM #varchar_field1
ORDER BY CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0
THEN mixed_field
ELSE '' END
,CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0
THEN 0
ELSE CAST(mixed_field AS DECIMAL(10,1)) END
DROP TABLE #varchar_field1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 18, 2012 at 12:24 am
dwain.c (12/17/2012)
Not very elegant but you might be able to use something like this, assuming your intent is to sort numbers first as numbers followed by non-numeric characters:
CREATE TABLE #varchar_field1 ( ID INT IDENTITY(1,1), mixed_field VARCHAR(100) )
INSERT INTO #varchar_field1 (mixed_field)
SELECT '1' UNION ALL SELECT '4.9' UNION ALL SELECT '10'
UNION ALL SELECT '50' UNION ALL SELECT '6' UNION ALL SELECT 'a'
UNION ALL SELECT 'z' UNION ALL SELECT 'A'
SELECT *
FROM #varchar_field1
ORDER BY CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0
THEN mixed_field
ELSE '' END
,CASE WHEN PATINDEX('[a-zA-Z]', mixed_field) > 0
THEN 0
ELSE CAST(mixed_field AS DECIMAL(10,1)) END
DROP TABLE #varchar_field1
Hi Dwaine,
The solution seems to work pretty well...I haven't used something like this before....could you explain the Order By part of the query a little.....would love to see.
December 18, 2012 at 12:36 am
I can try. 😉
The ORDER BY is split into two levels:
1. First the characters, which get assigned the empty string when the value is a numeric (which will sort before any characters).
2. Then the numbers, which I've converted to a number so they sort in numeric sequence.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 18, 2012 at 8:19 am
Well you did not specify that it was not sorting in the order you wanted, numeric first by value, character next. It sounded like it was an issue with inserts into a heap. My bad.
John Miner
Crafty DBA
www.craftydba.com
December 18, 2012 at 8:29 am
If you want to force the order by using the ID column, then force the ID column to have the values you want:
CREATE TABLE #varchar_field1
(ID INT IDENTITY(1, 1),
mixed_field VARCHAR(100));
SET IDENTITY_INSERT #varchar_field1 ON;
INSERT INTO #varchar_field1
(ID, mixed_field)
VALUES (1, '1'),
(2, '4.9'),
(3, '10'),
(4, '50'),
(5, '6'),
(6, 'a'),
(7, 'z'),
(8, 'A');
SET IDENTITY_INSERT #varchar_field1 OFF;
SELECT *
FROM #varchar_field1
ORDER BY ID;
Want to change the order? Change the insert so you have the right value with the right sequence (ID). Simple.
Even simpler, don't define an ID column. Define a "Sequence" column:
IF OBJECT_ID(N'tempdb..#varchar_field1') IS NOT NULL
DROP TABLE #varchar_field1;
CREATE TABLE #varchar_field1
(Sequence INT,
mixed_field VARCHAR(100));
INSERT INTO #varchar_field1
(Sequence, mixed_field)
VALUES (1, '1'),
(2, '4.9'),
(3, '10'),
(4, '50'),
(5, '6'),
(6, 'a'),
(7, 'z'),
(8, 'A');
SELECT *
FROM #varchar_field1
ORDER BY Sequence;
That makes it clear what the column is being used for, and doesn't require Identity_Insert to be set to On.
- 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply