Max value in the table

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 21, 2012 at 12:38 pm

  #271526

  Hello all,

  I was expecting the maximum value for the code mentioned below to be "005", but it is different,

  Could someone explain, If hyphen "-" is preceded in the sort order?

  USE tempdb;

  IF OBJECT_ID('#t123') > 0

  DROP TABLE #t123;

  CREATE TABLE #t123

  (

  id VARCHAR (3)

  );

  INSERT INTO #t123

  SELECT '001'

  UNION ALL

  SELECT '005'

  UNION ALL

  SELECT '01-';

  SELECT Max(id)

  FROM #t123;

  DROP TABLE #t123;

  Thanks in advance.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 12:55 pm

  #1504166

  ssc_san (6/21/2012)

  ---

  Hello all,

  I was expecting the maximum value for the code mentioned below to be "005", but it is different,

  Could someone explain, If hyphen "-" is preceded in the sort order?

  USE tempdb;

  IF OBJECT_ID('#t123') > 0

  DROP TABLE #t123;

  CREATE TABLE #t123

  (

  id VARCHAR (3)

  );

  INSERT INTO #t123

  SELECT '001'

  UNION ALL

  SELECT '005'

  UNION ALL

  SELECT '01-';

  SELECT Max(id)

  FROM #t123;

  DROP TABLE #t123;

  Thanks in advance.

  Without running the code, I am assuming that the value you got back for max was '01-'. The hypen had nothing to do with it. The other two values both start with '00', and the '01' of the '01-' will be the max value.

  Can you explain in greater detail what the problem is that you are trying to solve?

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 21, 2012 at 1:00 pm

  #1504172

  Thanks Lynn for the reply.

  I am trying to extract a part of string and with in that I need the max value for ID associated to it. As the string which I am extracting from is not consistent which is pulling the hyphen's for a few records.

  any other approach to solve this issue?

  Thanks again.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 1:06 pm

  #1504175

  Not without greater detail. Please read and follow the instructions in the first article I reference below in my signature block. It will walk you through the things you should post and how to do it to get the best possible answers to your questions. Also, remember that sample data really should be just that sample data. Make it up, but be sure it is representative of your problem. Also, based on the sample data, be sure to post your expected resutls.

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  June 21, 2012 at 1:14 pm

  #1504184

  declare @t table (id varchar(3))

  insert into @t values ('001');

  insert into @t values ('005');

  insert into @t values ('01-');

  select max(id) from @t;

  select max( case when isnumeric(id)=1 then cast(id as int) else 0 end) from @t;

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 21, 2012 at 1:18 pm

  #1504188

  Bill, as he mentioned he wants to use only the numeric portions, not just exclude anything with a dash.

  Generically, you're going to end up using a LEFT() function to grab only the numeric portion with a CHARINDEX() to locate the dash position and strip off anything after it. As Lynn mentioned, if you can give us a sample DDL with some data that illustrates the issue, we can walk you through it.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 1:29 pm

  #1504194

  I also think there is more to it than what is in OPs initial post.

• ScottPletcher

  SSC Guru

  Points: 101093

  More actions

  June 21, 2012 at 1:29 pm

  #1504195

  If you only want to compare based on the leading numeric characters (0-9 only), you can do this (I prefer PATINDEX to CHARINDEX in this case, as it handles all non-numerics easily):

  SELECT Max(CAST(LEFT(id, CASE WHEN PATINDEX('%[^0-9]%', id) = 0 THEN 100 ELSE PATINDEX('%[^0-9]%', id) - 1 END) AS int))

  FROM #t123;

  The 100 is just an arbitrary number guaranteed to be at least as long or longer than the entire column -- you can use 8000 if you want.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 21, 2012 at 2:36 pm

  #1504232

  Thank you all for the replies.

  Here is the sample code, I am trying to get the Max three letter field and date associated to it, the query mentioned in the code gives me the "01-" as MAX field where I was expecting "007" and "06/30/2012".

  USE tempdb;

  IF OBJECT_ID('#t345') > 0

  DROP TABLE #t345;

  CREATE TABLE #t345

  (

  id VARCHAR (100),

  dt DATETIME

  );

  INSERT INTO #t345

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZCONT',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZPROP',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZTEMP',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZOTHE',

  GETDATE() - 6

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBRP',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBHP',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBTR',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFM',

  GETDATE() - 9

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBPR',

  GETDATE() - 11

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFC',

  GETDATE() - 21

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLB',

  GETDATE() - 14

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI01-ABC_XYZINTLBOT',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZPROCURE',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZARCHENG',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZSITSTUD',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZMAPSU',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZAGREIGA',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZOTHERSS',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBHP',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBTR',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZPROCURE',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZINTLBOT',

  GETDATE() - 43

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZCONM',

  GETDATE() - 15

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZARCHE',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 12

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 34

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005-ABC_XYZINTLBHP',

  GETDATE() - 32

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI006',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007',

  GETDATE() + 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZDESC',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZCONC',

  GETDATE() + 9;

  SELECT *

  FROM #t345;

  SELECT SUBSTRING(id, 29, 10) AS Account,

  MAX(Field),

  Max(dt)

  FROM (SELECT SUBSTRING(id, 39, 3) AS Field,

  *

  FROM #t345) AS A

  GROUP BY SUBSTRING(id, 29, 10);

  DROP TABLE #t345;

  Thanks again.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 2:42 pm

  #1504235

  ssc_san (6/21/2012)

  ---

  Thank you all for the replies.

  Here is the sample code, I am trying to get the Max three letter field and date associated to it, the query mentioned in the code gives me the "01-" as MAX field where I was expecting "007" and "06/30/2012".

  USE tempdb;

  IF OBJECT_ID('#t345') > 0

  DROP TABLE #t345;

  CREATE TABLE #t345

  (

  id VARCHAR (100),

  dt DATETIME

  );

  INSERT INTO #t345

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZCONT',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZPROP',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZTEMP',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZOTHE',

  GETDATE() - 6

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBRP',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBHP',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBTR',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFM',

  GETDATE() - 9

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBPR',

  GETDATE() - 11

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFC',

  GETDATE() - 21

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLB',

  GETDATE() - 14

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI01-ABC_XYZINTLBOT',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZPROCURE',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZARCHENG',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZSITSTUD',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZMAPSU',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZAGREIGA',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZOTHERSS',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBHP',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBTR',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZPROCURE',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZINTLBOT',

  GETDATE() - 43

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZCONM',

  GETDATE() - 15

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZARCHE',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 12

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 34

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005-ABC_XYZINTLBHP',

  GETDATE() - 32

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI006',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007',

  GETDATE() + 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZDESC',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZCONC',

  GETDATE() + 9;

  SELECT *

  FROM #t345;

  SELECT SUBSTRING(id, 29, 10) AS Account,

  MAX(Field),

  Max(dt)

  FROM (SELECT SUBSTRING(id, 39, 3) AS Field,

  *

  FROM #t345) AS A

  GROUP BY SUBSTRING(id, 29, 10);

  DROP TABLE #t345;

  Thanks again.

  Here is my problem, I would expect you want '010' not '007' if the '-' in '01-' were a '0', why would you expect '01-' to be less than '007'?

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 21, 2012 at 3:03 pm

  #1504254

  Here is my problem, I would expect you want '010' not '007' if the '-' in '01-' were a '0', why would you expect '01-' to be less than '007'?

  Lynn,

  User entered it as "01", instead of "001".

  Thanks.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 3:08 pm

  #1504255

  ssc_san (6/21/2012)

  ---

  Here is my problem, I would expect you want '010' not '007' if the '-' in '01-' were a '0', why would you expect '01-' to be less than '007'?

  Lynn,

  User entered it as "01", instead of "001".

  Thanks.

  How is a SQL query supposed to know this? Are these three columns supposed to contain values between 000 and 999?

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 21, 2012 at 3:15 pm

  #1504259

  Lynn Pettis (6/21/2012)

  ---

  ssc_san (6/21/2012)

  ---

  Here is my problem, I would expect you want '010' not '007' if the '-' in '01-' were a '0', why would you expect '01-' to be less than '007'?

  Lynn,

  User entered it as "01", instead of "001".

  Thanks.

  How is a SQL query supposed to know this? Are these three columns supposed to contain values between 000 and 999?

  The values can be 001 - 007, 053, 055 (Can add more values in future). I need to get the maximum date associated to the maximum value. So for the account mentioned in the code above the max field is 007 and date 06/302012.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 21, 2012 at 3:20 pm

  #1504262

  ssc_san (6/21/2012)

  ---

  Lynn Pettis (6/21/2012)

  ---

  ssc_san (6/21/2012)

  ---

  Here is my problem, I would expect you want '010' not '007' if the '-' in '01-' were a '0', why would you expect '01-' to be less than '007'?

  Lynn,

  User entered it as "01", instead of "001".

  Thanks.

  How is a SQL query supposed to know this? Are these three columns supposed to contain values between 000 and 999?

  The values can be 001 - 007, 053, 055 (Can add more values in future). I need to get the maximum date associated to the maximum value. So for the account mentioned in the code above the max field is 007 and date 06/302012.

  You haven't answered the question, how does a SQL query know that '01-' is supposed to be '001' not '010'? You have said that '010' is not a valid value, at this time but that it could change.

  The following code makes the assumtion that if there is '-' in the value, prepend a '0' to the value and remove the '-'.

  USE SandBox;

  CREATE TABLE #t345

  (

  id VARCHAR (100),

  dt DATETIME

  );

  GO

  INSERT INTO #t345

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZCONT',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZPROP',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZTEMP',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZOTHE',

  GETDATE() - 6

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBRP',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBHP',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBTR',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFM',

  GETDATE() - 9

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBPR',

  GETDATE() - 11

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLBFC',

  GETDATE() - 21

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI001-ABC_XYZINTLB',

  GETDATE() - 14

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI01-ABC_XYZINTLBOT',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZPROCURE',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZARCHENG',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZSITSTUD',

  GETDATE() - 5

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZMAPSU',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZAGREIGA',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZOTHERSS',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002',

  GETDATE() - 13

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBHP',

  GETDATE() - 7

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI002-ABC_XYZINTLBTR',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 2

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZPROCURE',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003',

  GETDATE() - 8

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI003-ABC_XYZINTLBOT',

  GETDATE() - 43

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 19

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZCONM',

  GETDATE() - 15

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004-ABC_XYZARCHE',

  GETDATE() - 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 12

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI004',

  GETDATE() - 10

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005',

  GETDATE() - 34

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI005-ABC_XYZINTLBHP',

  GETDATE() - 32

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI006',

  GETDATE() - 1

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007',

  GETDATE() + 4

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZDESC',

  GETDATE() - 3

  UNION ALL

  SELECT '****-1234-ABC_4321-****-ABC_DEF.XYZGHI007-ABC_XYZCONC',

  GETDATE() + 9;

  GO

  SELECT *

  FROM #t345;

  GO

  SELECT SUBSTRING(id, 29, 10) AS Account,

  MAX(Field),

  Max(dt)

  FROM (SELECT RIGHT('0' + REPLACE(SUBSTRING(id, 39, 3),'-',''), 3) AS Field,

  *

  FROM #t345) AS A

  GROUP BY SUBSTRING(id, 29, 10);

  GO

  DROP TABLE #t345;

  GO

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  June 21, 2012 at 3:26 pm

  #1504264

  If these fields are all the exact same length than what's posted above by Lynn will work. I'm afraid I don't necessarily agree with Lynn about 01 vs. 010 vs. 001, but that'll depend exactly on your formatting expectations.

  That said, if these fields AREN'T always the same length/positions, then you're going to need to get pretty fancy with Reverse(), testing if the . or the - is found earliest in the reversed line (so you can figure out if you're dealing with appended data to the value you want), then trimming the string and re-reversing depending on what you find.

  So, before I go through all that... are those ****s considered variable length wildcards or are these values always at the exact same position in the string?

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA