Query help - 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

Query help

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-05-13 : 02:29:31

I need some help building a query that returns only ONE uenr based on the highest valnr for each typecode.

CREATE TABLE table1(
uenr nvarchar(10) not NULL,
valnr int not null,
typecode char(1) not null,
data nvarchar(100) not null
)

insert into table1(uenr, valnr, typecode, data) values ('102030', 1, 'F', 'blahahhh');
insert into table1(uenr, valnr, typecode, data) values ('112131', 2, 'F', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('122232', 3, 'L', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('132333', 4, 'L', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('162030', 5, 'S', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('102030', 6, 'F', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('132333', 7, 'F', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('142535', 8, 'S', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('112131', 9, 'L', 'blahahhh')
insert into table1(uenr, valnr, typecode, data) values ('122232', 10, 'F', 'blahahhh')

Expected output:
102030, 6, F, blahahhh
112131, 9, L, blahahhh
122232, 10, F, blahahhh
132333, 7, L, blahahhh
162030, 5, S, blahahhh
142535, 8, S, blahahhh

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-13 : 02:38:20

Hi,

Can u explain what u want in detail

Be cool...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 02:39:50

Are you using Microsoft SQL Server 2000 or Microsoft SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

ranganath
Posting Yak Master

209 Posts

Posted - 2008-05-13 : 02:43:19

Hi,

try with this

Declare @Table1 TABLE (
uenr nvarchar(10) not NULL,
valnr int not null,
typecode char(1) not null,
data nvarchar(100) not null
)

insert into @Table1(uenr, valnr, typecode, data) values ('102030', 1, 'F', 'blahahhh');
insert into @Table1(uenr, valnr, typecode, data) values ('112131', 2, 'F', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('122232', 3, 'L', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('132333', 4, 'L', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('162030', 5, 'S', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('102030', 6, 'F', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('132333', 7, 'F', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('142535', 8, 'S', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('112131', 9, 'L', 'blahahhh')
insert into @Table1(uenr, valnr, typecode, data) values ('122232', 10, 'F', 'blahahhh')

Select Cast(a.uenr as varchar(100))+ ',' + Cast(a.valnr as varchar(100)) + ',' + a.typecode + ',' + a.data
From @Table1 A
Inner join (Select uenr , Max(valnr) as valnr From @Table1 Group By uenr ) AS B on A.valnr = B.valnr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 02:46:51

[code]SELECT t1.*
FROM #table1 t1
INNER JOIN (SELECT uenr,max(valnr) AS maxval
FROM #table1
GROUP BY uenr)t2
ON t2.uenr=t1.uenr
AND t2.maxval=t1.valnr
order by t1.uenr

output
-------------------------------------------------

uenr valnr typecode data
---------- ----------- -------- -------------------------------------
102030 6 F blahahhh
112131 9 L blahahhh
122232 10 F blahahhh
132333 7 F blahahhh
142535 8 S blahahhh
162030 5 S blahahhh

[/code]

maevr
Posting Yak Master

169 Posts

Posted - 2008-05-13 : 03:58:37

Problem solved!
Thanks for all help, very fast reply :-).

use sql server 2005!

renu
Starting Member

47 Posts

Posted - 2008-05-13 : 05:01:35

select uenr,valnr,typecode,data
from table1
where uenr in (select uenr
from table1
group by uenr)
and valnr in (select max(valnr)as valnr
from table1
group by uenr)
order by uenr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources