Max Question - SQL Server Forums

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

Max Question

Author

Topic

Otacustes
Starting Member

15 Posts

Posted - 2008-09-04 : 08:26:40

Hi all

I have a test table in MS Access as shown below:


Issue	MachineNo
1	ABC
1	KLM
1	ABC
2	ABC
3	ABC
3	ABC
4	ABC
4	ABC
4	ABC
5	HIJ
5	ABC
5	ABC
6	ABC
6	ABC
6	ABC
8	ABC
8	ABC
8	ABC
7	DEF
7	ABC
9	ABC
9	ABC
10	DEF
40	ABC
23	ABC
23	ABC
23	DEF
40	ABC

To try and find the max Issue for a particular MachineNo I used this:


SELECT Max(Issue) AS [Max Issue], MachineNo
FROM tblMachineIssues
WHERE (MachineNo='ABC')
GROUP BY MachineNo;

...but this is what I actually got:


MachineNo	What I got	What it should be
ABC		 9			40
DEF 		 7			23
HIJ 		 5			5
KLM		 1			1

Can anyone tell me where I have gone wrong please? HIJ and KLM are correct so i'm thinking it must be something simple??

Any help or guidance offered is greatly appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-04 : 08:35:02

because column Issue is string not numeric

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 08:37:03

Why did you use text datatype to store numbers?

SELECT Max(Issue*1) AS [Max Issue], MachineNo
FROM tblMachineIssues
WHERE (MachineNo='ABC')
GROUP BY MachineNo;

Madhivanan

Failing to plan is Planning to fail

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-04 : 08:37:44

I tried this and got max value correctly.

Declare @table table
(
issue int,
mno varchar (50)
)
INSERT INTO @table
select 1,'abc' union all
select 20,'bcd' union all
select 45,'abc' union all
select 50,'abc' union all
select 2,'bcd'
select MAX(issue) AS ISSUE,mno from @Table where mno='abc' group by mno

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-04 : 08:43:12

quote:
Originally posted by sunil

I tried this and got max value correctly.

Declare @table table
(
issue int,
mno varchar (50)
)
INSERT INTO @table
select 1,'abc' union all
select 20,'bcd' union all
select 45,'abc' union all
select 50,'abc' union all
select 2,'bcd'
select MAX(issue) AS ISSUE,mno from @Table where mno='abc' group by mno

OP wont as the datatype is text

Madhivanan

Failing to plan is Planning to fail

Otacustes
Starting Member

15 Posts

Posted - 2008-09-04 : 08:45:44

quote:
Originally posted by madhivanan

Why did you use text datatype to store numbers?

SELECT Max(Issue*1) AS [Max Issue], MachineNo
FROM tblMachineIssues
WHERE (MachineNo='ABC')
GROUP BY MachineNo;

Madhivanan

Failing to plan is Planning to fail

ok, I see ;) Funnily enough changing issue to number seems to do the trick.

Out of curiousity how did you know issue was not a number? There as nothing in the test table or the query that indicated what data type they were, or was there?

Any help or guidance offered is greatly appreciated.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-04 : 08:48:18

the only reason that max() return 9 rather than 40 is the column is not a numeric but string

KH
[spoiler]Time is always against us[/spoiler]

Otacustes
Starting Member

15 Posts

Posted - 2008-09-04 : 08:55:04

quote:
Originally posted by khtan

the only reason that max() return 9 rather than 40 is the column is not a numeric but string

KH
[spoiler]Time is always against us[/spoiler]

I see.

Thanks for the help guys

Any help or guidance offered is greatly appreciated.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources