TSQL PRoblm
-
I have a table with following data col name refNo
refNo
---
ABC-0
ABC-1
XYZ-0
XYZ-1
XYZ-2
PQR-0
MNO-0
MNO-1
SPX-0
SPX-1
I want the row only with the maximum value at last
how can i do?
can anyone help me?
thankx in advance..
San
-
Not sure if this is what you mean, but you can try this:
SELECT TOP 1 REF_NO
FROM REF
ORDER BY SUBSTRING(REF_NO,CHARINDEX('-',REF_NO),99) DESC
-
i want the result as below
ABC-1
XYZ-2
PQR-0
MNO-1
SPX-1
-
-- Test data
declare @t table
(
refNo char(5) not null primary key
)
insert @t
select 'ABC-0' union all
select 'ABC-1' union all
select 'XYZ-0' union all
select 'XYZ-1' union all
select 'XYZ-2' union all
select 'PQR-0' union all
select 'MNO-0' union all
select 'MNO-1' union all
select 'SPX-0' union all
select 'SPX-1'
-- Query
select refNo
from @t T1
where right(T1.refNo, 1) =
(select max(right(refNo,1))
from @t T2
where left(T2.refNo, 3) = left(T1.refNo, 3) )
-
Here's another method.. the charindex is necessary if you don't know the number of characters before or after the '-':
-- using Ken McKelvey's test date table
declare @t table
(
refNo char(5) not null primary key
)
insert @t
select 'ABC-0' union all
select 'ABC-1' union all
select 'XYZ-0' union all
select 'XYZ-1' union all
select 'XYZ-2' union all
select 'PQR-0' union all
select 'MNO-0' union all
select 'MNO-1' union all
select 'SPX-0' union all
select 'SPX-1'
select letters + '-' + cast(maxNumber as varchar)
from (
select left(refNo,charindex('-', refNo)-1) as letters
, max(cast(substring(refNo,charindex('-',refNo)+1,10) as int)) as maxNumber
from @t
group by left(refNo,charindex('-', refNo)-1)
) TableA
-
Another one ...
SET NOCOUNT ON
DECLARE @tbl TABLE (COL1 VARCHAR(20))
INSERT INTO @tbl (Col1)
SELECT 'ABC-0'
UNION ALL
SELECT 'ABC-1'
UNION ALL
SELECT 'XYZ-0'
UNION ALL
SELECT 'XYZ-1'
UNION ALL
SELECT 'XYZ-2'
UNION ALL
SELECT 'PQR-0'
UNION ALL
SELECT 'MNO-0'
UNION ALL
SELECT 'MNO-1'
UNION ALL
SELECT 'SPX-0'
UNION ALL
SELECT 'SPX-1'
SELECT MAX(SUBSTRING(COL1,1,3))+'-'+CAST(MAX(SUBSTRING(Col1,5,5)) AS VARCHAR(10))
FROM @TBL
GROUP BY SUBSTRING(COL1,1,3)
-Ram
-
Hi,
Try this query to solve your problem .
/* Table Creation */
create table data
(
val_data varchar(10)
)
/* Inserting Data into Table */
insert into data
select 'ABC-0'
union all
select 'ABC-1'
union all
select 'XYZ-0'
union all
select 'XYZ-1'
union all
select 'XYZ-2'
union all
select 'PQR-0'
union all
select 'MNO-0'
union all
select 'MNO-1'
union all
select 'SPX-0'
union all
select 'SPX-1'
/* Query */
select a+'-'+b from (
select left(val_data,3)a,max(right(val_data,1))b from data
group by left(val_data,3))a
Thanks,
Amit Gupta
-
Actually, what I think what is being looked for is this:
create table dbo.Test1 (refNo varchar(10))
insert into dbo.Test1 (refNo) values ('ABC-0')
insert into dbo.Test1 (refNo) values ('ABC-1')
insert into dbo.Test1 (refNo) values ('XYZ-0')
insert into dbo.Test1 (refNo) values ('XYZ-1')
insert into dbo.Test1 (refNo) values ('XYZ-2')
insert into dbo.Test1 (refNo) values ('PQR-0')
insert into dbo.Test1 (refNo) values ('MNO-0')
insert into dbo.Test1 (refNo) values ('MNO-1')
insert into dbo.Test1 (refNo) values ('SPX-0')
insert into dbo.Test1 (refNo) values ('SPX-1')
select * from dbo.Test1
select
t1.refNo
from
dbo.Test1 t1
where
t1.refNo = ( select
max(t2.refNo)
from
dbo.Test1 t2
where
substring(t2.refNo,1, charindex('-',t2.refNo) - 1) =
substring(t1.refNo,1, charindex('-',t1.refNo) - 1))
hth,
Lynn
-
Lynn,
The problem there is the MAX function is still operating on a varchar, so if you have XYZ-11 in the table, XYZ-2 will show as greater than XYZ-11.
We're assuming the original poster wants 11 to be greater than 2.
-
San,
Actually the best solution is the following:
DROP TABLE refNo; DECLARE @email NVARCHAR(256), @message NVARCHAR(2048), @subject NVARCHAR(128); SELECT @email = 'boss@yourcompany.com', @subject = 'I resign', @message = 'Dear Boss,' + char(10) + char(13) char(10) + char(13) + 'There are many solutions to the problem you gave me. Unfortunately, even the ' + 'best minds on SqlServerCentral.com cannot decide which solution to apply. ' + 'In fact, I think they are simply attempting to increase their post count. ' + 'Regardless, I am tired of these petty SQL problems you send me every hour ' + 'of every day. I want something more rewarding in my career. So, please accept ' + 'my resignation.' + char(10) + char(13) + char(10) + char(13) + 'Sincerely,' + char(10) + char(13) + 'San' + char(10) + char(13); EXEC xp_sendmail @email, @message, @subject = @subject; -
Just to add to my post count and assuming that the sort must be numerical.
create
table dbo.Test1 (refNo varchar(10))
insert
into dbo.Test1 (refNo) values ('ABC-0')
insert
into dbo.Test1 (refNo) values ('ABC-1')
insert
into dbo.Test1 (refNo) values ('XYZ-0')
insert
into dbo.Test1 (refNo) values ('XYZ-11')
insert
into dbo.Test1 (refNo) values ('XYZ-2')
insert
into dbo.Test1 (refNo) values ('PQR-0')
insert
into dbo.Test1 (refNo) values ('MNO-0')
insert
into dbo.Test1 (refNo) values ('MNO-011')
insert
into dbo.Test1 (refNo) values ('SPX-0')
insert
into dbo.Test1 (refNo) values ('SPX-1')
select
* from dbo.Test1
select
t1
.refNo
from
dbo
.Test1 t1
where
t1
.refNo = ( select top 1 t2.RefNo
from
dbo
.Test1 t2
where
substring(t2.refNo,1, charindex('-',t2.refNo) - 1) =
substring(t1.refNo,1, charindex('-',t1.refNo) - 1)
order by CAST(substring(t2.refNo ,charindex('-',t2.refNo) + 1, len(t2.refNo)) AS INT) DESC)
drop
table Test1
-
In the example provided above by RDR'us, instead of using the final SELECT proposed, you could use the following two queries with a performance gain of approximately 23.11%! How? When the 2 queries below are executed side-by-side with the SELECT above, you will notice that their combined query cost is 35.84% compared with 58.95% of the single SELECT. Sometimes it's better to split things up...
SELECT refNo, SUBSTRING(refNo, 1, CHARINDEX('-', refNo) - 1) AS char_code, CAST(SUBSTRING(refNo, CHARINDEX('-', refNo) + 1, LEN(refNo)) AS INT) AS ordinal INTO #ordered FROM dbo.Test1; SELECT DISTINCT refNo FROM #ordered o INNER JOIN ( SELECT char_code, MAX(ordinal) as ordinal FROM #ordered GROUP BY char_code ) omax ON o.char_code = omax.char_code AND o.ordinal = omax.ordinal; DROP TABLE #ordered; -
Greg,
You are right, so I made a change to mine, but I'm sure some of the other solutions may be just as good:
create table dbo.Test1 (refNo varchar(10))
insert into dbo.Test1 (refNo) values ('ABC-0')
insert into dbo.Test1 (refNo) values ('ABC-1')
insert into dbo.Test1 (refNo) values ('ABC-12')
insert into dbo.Test1 (refNo) values ('XYZ-0')
insert into dbo.Test1 (refNo) values ('XYZ-1')
insert into dbo.Test1 (refNo) values ('XYZ-2')
insert into dbo.Test1 (refNo) values ('PQR-0')
insert into dbo.Test1 (refNo) values ('MNO-0')
insert into dbo.Test1 (refNo) values ('MNO-1')
insert into dbo.Test1 (refNo) values ('SPX-0')
insert into dbo.Test1 (refNo) values ('SPX-1')
select * from dbo.Test1
select
t1.refNo
from
dbo.Test1 t1
where
cast(substring(t1.refNo, charindex('-',t1.refNo) + 1, len(t1.refNo) - charindex('-',t1.refNo)) as int) = ( select
max(cast(substring(t2.refNo, charindex('-',t2.refNo) + 1, len(t2.refNo) - charindex('-',t2.refNo)) as int))
from
dbo.Test1 t2
where
substring(t2.refNo,1, charindex('-',t2.refNo) - 1) =
substring(t1.refNo,1, charindex('-',t1.refNo) - 1))
-
San,
Surely you've got your answer by now, eh?
-
Oh you want it fast to!!!!!!!!!
Short of completely changing the design of the table (which seems it might be a good idea here?!?) :
create table dbo.Test1 (refNo varchar(10) primary key clustered)
insert into dbo.Test1 (refNo) values ('ABC-0')
insert into dbo.Test1 (refNo) values ('ABC-1')
insert into dbo.Test1 (refNo) values ('ABC-12')
insert into dbo.Test1 (refNo) values ('XYZ-0')
insert into dbo.Test1 (refNo) values ('XYZ-1')
insert into dbo.Test1 (refNo) values ('XYZ-2')
insert into dbo.Test1 (refNo) values ('PQR-0')
insert into dbo.Test1 (refNo) values ('MNO-0')
insert into dbo.Test1 (refNo) values ('MNO-1')
insert into dbo.Test1 (refNo) values ('SPX-0')
insert into dbo.Test1 (refNo) values ('SPX-1')
select * from dbo.Test1
ALTER TABLE dbo.Test1
ADD Ref AS substring(refNo,1, charindex('-',refNo) - 1),
Num AS cast(substring(refNo, charindex('-',refNo) + 1, len(refNo) - charindex('-',refNo)) as int)
CREATE UNIQUE INDEX IX_Test1_Cov_Ref_Num ON dbo.Test1 (Ref, Num)
Select Ref + '-' + CAST(MAX(Num) as varchar(10)) as RefNo from dbo.Test1 group by Ref
select
t1.refNo
from
dbo.Test1 t1
where
cast(substring(t1.refNo, charindex('-',t1.refNo) + 1, len(t1.refNo) - charindex('-',t1.refNo)) as int) = ( select
max(cast(substring(t2.refNo, charindex('-',t2.refNo) + 1, len(t2.refNo) - charindex('-',t2.refNo)) as int))
from
dbo.Test1 t2
where
substring(t2.refNo,1, charindex('-',t2.refNo) - 1) =
substring(t1.refNo,1, charindex('-',t1.refNo) - 1))
DROP TABLE dbo.Test1
This new solution wins 7 to 3 (30% vs 70% for the last version of Lynn)
But Eric's solution is still slightly faster (with the added index anyways). Thanx for the tip
.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply