Help in query max of a particular record

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help in query max of a particular record

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-03-08 : 02:43:19

Suppose I have a table with three field
jcode jnumber jstatus
1 123 1
1 323 1
1 434 5
1 213 5
2 324 1
2 343 5
3 344 5
3 343 5
4 23 2
4 232 3
4 001 3

I want's each code with maximum jnumber

jcode jnumber jstatus
1 434 5
2 343 5
3 344 5
4 232 3

Please help me in the select sql

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 02:48:46

[code]
select t.*
from table1 t
inner join
(
select jcode, max_jnumber = max(jnumber)
from table1
group by jcode
) m
on t.jcode = m.jcode
and t.jnumber = m.max_jnumber
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 03:54:30

Oops!
'23' > '1001'

TRUE...

It seems JNumber is stored as VARCHAR!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 03:59:13

[code]-- prepare sample data
declare @t table (jcode int, jnumber varchar(4), jstatus int)

insert @t
select 1, '123', 1 union all
select 1, '323', 1 union all
select 1, '434', 5 union all
select 1, '213', 5 union all
select 2, '324', 1 union all
select 2, '343', 5 union all
select 3, '344', 5 union all
select 3, '343', 5 union all
select 4, '23', 2 union all
select 4, '232', 3 union all
select 4, '001', 3

-- Show the output
SELECT t1.jCode,
t1.jNumber,
t1.jStatus
FROM @t AS t1
WHERE t1.jNumber = (SELECT TOP 1 t2.jNumber FROM @t AS t2 WHERE t1.jCode = t2.jCode ORDER BY LEN(t2.jNumber) DESC, t2.jNumber DESC)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 04:56:21

And with SQL Server 2005 (if you consider the move)

-- prepare sample data
declare @t table (jcode int, jnumber varchar(4), jstatus int)

insert	@t
select	1, '123', 1 union all
select	1, '323', 1 union all
select	1, '434', 5 union all
select	1, '213', 5 union all
select	2, '324', 1 union all
select	2, '343', 5 union all
select	3, '344', 5 union all
select	3, '343', 5 union all
select	4, '23', 2 union all
select	4, '232', 3 union all 
select	4, '001', 3

-- Show the output
SELECT	jCode,
	jNumber,
	jStatus
FROM	(
		SELECT	jCode,
			jNumber,
			jStatus,
			ROW_NUMBER() OVER (PARTITION BY jCode ORDER BY LEN(jNumber) DESC, jNumber DESC) AS RecID
		FROM	@t
	) AS x
WHERE	RecID = 1

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources