0

i want to select MAX Id where i have the list database

s001.1
s001.2
s001.3
s001.4
s002.1
s002.2
s002.3
s002.4
upto so On

my field type is char(7)

MSQL SELECT MAX(sup_id) where data type is char(7)

plz help me out to perform this query in SQL.

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by tjaank
0

try this:

declare @temp TABLE
(this char(7))
INSERT INTO @temp VALUES('s001.1')
INSERT INTO @temp VALUES('s001.2')
INSERT INTO @temp VALUES('s001.3')
INSERT INTO @temp VALUES('s001.4')
INSERT INTO @temp VALUES('s002.2')
INSERT INTO @temp VALUES('s002.3')
INSERT INTO @temp VALUES('s003.3')

SELECT 's' + CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS VARCHAR)
FROM @temp
0

When it return max in this condition s003.3.Then i want to increment in S003.3 S003.4 and so on dynamically parametrically.Please give me suggestion how to achieve.Thanks

1

i did'nt understand what do you want. but may be u want something like this:

declare @temp TABLE
(this char(7))
INSERT INTO @temp VALUES('s001.1')
INSERT INTO @temp VALUES('s001.2')
INSERT INTO @temp VALUES('s001.3')
INSERT INTO @temp VALUES('s001.4')
INSERT INTO @temp VALUES('s002.2')
INSERT INTO @temp VALUES('s002.3')
INSERT INTO @temp VALUES('s003.3')

SELECT 's00' + CAST((CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS DECIMAL(18,1)) + 0.1) AS VARCHAR)
--SELECT 's' + CAST(MAX(SUBSTRING(this, 2, LEN(this)-1)) AS VARCHAR)
FROM @temp

Edited by tjaank: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.