How to Filter Highest String Value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Filter Highest String Value

Author

Topic

Arma
Starting Member

10 Posts

Posted - 2010-07-15 : 13:05:48

How can I select highest value from M to M table.
I have something like this.

1 R0
3 R7
4 R5
4 R6
4 R7
2 R0
Do I have to convert char into int first?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 13:10:25

Whats your expected output ?

Arma
Starting Member

10 Posts

Posted - 2010-07-15 : 13:15:04

I want to show just highest value

1 R0
3 R7
4 R5 not this one
4 R6 not this one
4 R7
2 R0

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 13:21:31

select <firstcol>, max(<column to be sorted upon>) from <your table>
Group by <firstcol>,

Small example for your understanding .

Declare @Sample Table
(Srno int,
SVal varchar(50))

Insert into @Sample
Select 1 ,'R0' union all
Select 3 ,'R7' union all
Select 4 ,'R5' union all
Select 4 ,'R6' union all
Select 4 ,'R7' union all
Select 2 ,'R0'

select Srno, max(Sval) from @Sample
Group by srno

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Arma
Starting Member

10 Posts

Posted - 2010-07-15 : 13:23:14

Thanx

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-15 : 13:29:35

Welcome

I am here to learn from Masters and help new bees in learning.

Arma
Starting Member

10 Posts

Posted - 2010-07-16 : 09:51:49

How can I do this if I have 4 connected tables in this query.

SELECT dbo.TrainingEmployee.RevNumber AS MY_REV_No, dbo.RequiredTrainings.TrainingNumber, dbo.Training.Tcode,
dbo.Training.RevNumber AS QA_REV_No
FROM dbo.RequiredTrainings INNER JOIN
dbo.Employee ON dbo.RequiredTrainings.PositionNum = dbo.Employee.PositionNum LEFT OUTER JOIN
dbo.TrainingEmployee ON dbo.Employee.EmpNum = dbo.TrainingEmployee.EmpNum AND
dbo.RequiredTrainings.TrainingNumber = dbo.TrainingEmployee.TrainingNumber LEFT OUTER JOIN
dbo.Training ON dbo.RequiredTrainings.TrainingNumber = dbo.Training.TrainingNumber
WHERE (dbo.Employee.EmpNum = 6785)
GROUP BY dbo.Training.Tcode, dbo.TrainingEmployee.RevNumber, dbo.RequiredTrainings.TrainingNumber, dbo.Training.RevNumber

R2 117 MIS-001 R2
R2 169 MIS-002 R2
R6 144 MTN-020 R7
R0 180 QAC-005 R1
R9 83 QAC-044 R11
R3 156 QAC-050 R4
NULL 154 QAC-070 R9
NULL 192 QAC-106 R1
NULL 113 TRN-001 R12
R5 114 TRN-002 R7
R6 114 TRN-002 R7
NULL 179 TRN-003 R2

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 11:27:14

quote:

R2 117 MIS-001 R2
R2 169 MIS-002 R2
R6 144 MTN-020 R7
R0 180 QAC-005 R1
R9 83 QAC-044 R11
R3 156 QAC-050 R4
NULL 154 QAC-070 R9
NULL 192 QAC-106 R1
NULL 113 TRN-001 R12
R5 114 TRN-002 R7
R6 114 TRN-002 R7
NULL 179 TRN-003 R2

Out of the above, which are the rows that needs to be shown ?

Arma
Starting Member

10 Posts

Posted - 2010-07-16 : 11:40:51

Tabele has more than 6000 rows.
I want to show LAST one after grouping.
R5 114 TRN-002 R7 NOT THIS ONE.
R6 114 TRN-002 R7

I tried to convert this column into smallint and show MAX but I am getting error.

MAX(SUBSTRING(CONVERT smallint,(dbo.TrainingEmployee.RevNumber, 2, LEN(dbo.TrainingEmployee.RevNumber))))

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-07-16 : 12:29:48

Not sure but just give a try.
What ever the column is returning you R5 or R6, try doing max on it.

Arma
Starting Member

10 Posts

Posted - 2010-07-16 : 12:36:54

I have converted column into int and used MAX func.
No errors, but still it doesnot filter MACX value

SELECT TOP (100) PERCENT MAX(CONVERT(smallint, SUBSTRING(dbo.TrainingEmployee.RevNumber, 2, LEN(dbo.TrainingEmployee.RevNumber))))
AS MY_REV_No, dbo.RequiredTrainings.TrainingNumber, dbo.Training.Tcode, dbo.Training.RevNumber AS QA_REV_No
FROM dbo.RequiredTrainings INNER JOIN
dbo.Employee ON dbo.RequiredTrainings.PositionNum = dbo.Employee.PositionNum LEFT OUTER JOIN
dbo.TrainingEmployee ON dbo.Employee.EmpNum = dbo.TrainingEmployee.EmpNum AND
dbo.RequiredTrainings.TrainingNumber = dbo.TrainingEmployee.TrainingNumber LEFT OUTER JOIN
dbo.Training ON dbo.RequiredTrainings.TrainingNumber = dbo.Training.TrainingNumber
WHERE (dbo.Employee.EmpNum = 673)
GROUP BY dbo.Training.Tcode, dbo.TrainingEmployee.RevNumber, dbo.RequiredTrainings.TrainingNumber, dbo.Training.RevNumber
ORDER BY MY_REV_No DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources