January 21, 2015 at 3:55 am
Hi,
I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example
1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc
These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.
Does anyone have a script which will easily do this please?
January 21, 2015 at 4:14 am
Tex-166085 (1/21/2015)
Hi,I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example
1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc
These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.
Does anyone have a script which will easily do this please?
Quick solution (if I'm getting this right)
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)
VALUES
('1.0.0')
,('1.0.1')
,('1.1.0')
,('1.2.1')
,('2.0.0')
,('2.1.1')
;
SELECT
MAX(SD.THREE_PART_NUM)
FROM @SAMPLE_DATA SD;
Results
2.1.1
January 21, 2015 at 4:39 am
Eirikur Eiriksson (1/21/2015)
Tex-166085 (1/21/2015)
Hi,I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example
1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc
These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.
Does anyone have a script which will easily do this please?
Quick solution (if I'm getting this right)
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)
VALUES
('1.0.0')
,('1.0.1')
,('1.1.0')
,('1.2.1')
,('2.0.0')
,('2.1.1')
;
SELECT
MAX(SD.THREE_PART_NUM)
FROM @SAMPLE_DATA SD;
Results
2.1.1
add one more sample and see what will happen:
('12.1.1')
...
January 21, 2015 at 4:46 am
If your data always three-parts you can do:
SELECT TOP 1 THREE_PART_NUM
FROM @SAMPLE_DATA SD
ORDER BY CAST(PARSENAME(THREE_PART_NUM,3) AS INT) DESC
,CAST(PARSENAME(THREE_PART_NUM,2) AS INT) DESC
,CAST(PARSENAME(THREE_PART_NUM,1) AS INT) DESC;
if it is possible to have one part or two parts records eg. 10 or 10.1 then a bit more "complicated":
SELECT TOP 1 THREE_PART_NUM
FROM @SAMPLE_DATA SD
ORDER BY ISNULL(CAST(PARSENAME(THREE_PART_NUM,3) AS INT),2147483647) DESC
,ISNULL(CAST(PARSENAME(THREE_PART_NUM,2) AS INT),2147483647) DESC
,ISNULL(CAST(PARSENAME(THREE_PART_NUM,1) AS INT),2147483647) DESC;
January 21, 2015 at 4:47 am
January 21, 2015 at 4:49 am
Eugene Elutin (1/21/2015)
add one more sample and see what will happen:('12.1.1')
...
Elementary;-)
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
THREE_PART_NUM VARCHAR(10) NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)
VALUES
('1.0.0')
,('1.0.1')
,('1.1.0')
,('1.2.1')
,('2.0.0')
,('2.1.1')
,('12.1.1')
;
SELECT
MAX(CONVERT(BIGINT,REPLACE(SD.THREE_PART_NUM,CHAR(46),CHAR(48)),0))
FROM @SAMPLE_DATA SD;
Returns
120101
January 21, 2015 at 4:53 am
...
Elementary;-)
😎
Not quite so, try ('1.0.100')
😉
January 21, 2015 at 5:02 am
Hi,
wow, thanks for all the replies, I will work through them in my lunch hour. Yes, I cannot say how high each of the digits will go. It could be up to three, so 100.100.200 may be possible. I don't want to exclude the possibility, although that would be extreme.and extremely unlikely to go into 4 figures for each
Thanks again
January 21, 2015 at 5:11 am
Eugene Elutin (1/21/2015)
...
Elementary;-)
😎
Not quite so, try ('1.0.100')
😉
:pinch:
January 21, 2015 at 5:11 am
Tex-166085 (1/21/2015)
Hi,wow, thanks for all the replies, I will work through them in my lunch hour. Yes, I cannot say how high each of the digits will go. It could be up to three, so 100.100.200 may be possible. I don't want to exclude the possibility, although that would be extreme.and extremely unlikely to go into 4 figures for each
Thanks again
PARSENAME will work maximum for 4 parts.
If more parts will be involved, you will need to split string into parts by other means (check one of the best splitter functions implementations here: http://www.sqlservercentral.com/articles/Tally+Table/72993/)
Just note: If you want proper order, you need to treat each part as numeric separately or you need to reformat your string to something like:
0000.0000.0000
January 21, 2015 at 5:32 am
This is possibly an overkill but can easily be extended
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @SAMPLE_DATA TABLE
(
THREE_PART_NUM VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @SAMPLE_DATA (THREE_PART_NUM)
VALUES
('1000000001.0.00.0012345.012345.987654')
,('1000000001.0.10.1012345.012345.987654')
,('1000000001.1.00.0012345.012345.987654')
,('1000000001.2.10.1012345.012345.987654')
,('2000000001.0.00.0012345.012345.987654')
,('2000000001.1.10.1012345.012345.987654')
,('3147483647.1.10.1012345.012345.987654')
,('3147483647.11.0.1012345.012345.987654')
,('1000000000.0.10.1012345.012345.987654')
,('1000000002.1.10.1012345.012345.987654')
;
SELECT TOP (1)
SD.THREE_PART_NUM
,MAX(CASE WHEN X.ItemNumber = 1 THEN CONVERT(BIGINT,X.Item,0) END) AS C1
,MAX(CASE WHEN X.ItemNumber = 2 THEN CONVERT(BIGINT,X.Item,0) END) AS C2
,MAX(CASE WHEN X.ItemNumber = 3 THEN CONVERT(BIGINT,X.Item,0) END) AS C3
,MAX(CASE WHEN X.ItemNumber = 4 THEN CONVERT(BIGINT,X.Item,0) END) AS C4
,MAX(CASE WHEN X.ItemNumber = 5 THEN CONVERT(BIGINT,X.Item,0) END) AS C5
,MAX(CASE WHEN X.ItemNumber = 6 THEN CONVERT(BIGINT,X.Item,0) END) AS C6
FROM @SAMPLE_DATA SD
OUTER APPLY dbo.DelimitedSplit8K(SD.THREE_PART_NUM,CHAR(46)) AS X
GROUP BY SD.THREE_PART_NUM
ORDER BY C1 DESC,C2 DESC,C3 DESC,C4 DESC,C5 DESC,C6 DESC;
Results
THREE_PART_NUM C1 C2 C3 C4 C5 C6
-------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
3147483647.11.0.1012345.012345.987654 3147483647 11 0 1012345 12345 987654
January 21, 2015 at 6:33 am
What about some change makes it to be 7-parts? Then, few months later, 8-parts?
If you want to be dynamic and a bit more simpler, do this:
SELECT TOP 1 SD.THREE_PART_NUM
FROM @SAMPLE_DATA SD
ORDER BY (SELECT RIGHT('0000000000' + x.item,10) + '.'
FROM dbo.DelimitedSplit8K(SD.THREE_PART_NUM,CHAR(46)) AS X
ORDER BY x.ItemNumber
FOR XML PATH('')) DESC
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply