select query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-10-07 : 04:38:24

Dear Member

following is my table structure

cust ordno odrqty delqty pos
A 1111 10 4 1
A 1111 10 5 2
A 1111 10 1 3
A 1111 10 0 4
A 1112 20 10 1
A 1112 20 5 2
A 1112 20 5 3
B 1113 10 5 1
B 1113 10 5 2
B 1114 20 10 1
B 1114 20 5 2
B 1114 10 5 3

I wan't to select record which pos no is high in one select query.
so how can write select query
following is sample results i wan't

cust ordno odrqty delqty pos
A 1111 10 0 4
A 1112 20 5 3
B 1113 10 5 2
B 1114 10 5 3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-07 : 04:46:37

[code]
select cust, ordno, odrqty, delqty, pos from
(select cust, ordno, odrqty, delqty, pos,
row_number() over (partition by cust,ordno order by pos DESC) as rownum
from YourTable)dt
where rownum = 1
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 04:46:41

[code]SELECT cust, ordno, odrqty, delqty, pos
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY cust,ordno ORDER BY pos DESC) AS rn,cust, ordno, odrqty, delqty, pos
FROM yourtable
)t
WHERE rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:47:11

select *
from tbl t
where pos = (select max(pos) from tbl t2 where t.cust = t2.cust and t.orderno = t2.orderno)

select * from
(
select *, seq = rownumber() over (partition by cust, orderno order by pos desc)
from tbl
) a
where seq = 1

oops = v2005 - not supported - use the first one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 04:47:21

by 4 sec

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-07 : 04:54:34

quote:
Originally posted by nigelrivett

select *
from tbl t
where pos = (select max(pos) from tbl t2 where t.cust = t2.cust and t.orderno = t2.orderno)

select * from
(
select *, seq = rownumber() over (partition by cust, orderno order by pos desc)
from tbl
) a
where seq = 1

oops = v2005 - not supported - use the first one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

It is supported in 2005.
ROW_NUMBER() ~~rownumber()~~

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-07 : 04:56:12

quote:
Originally posted by webfred

quote:
Originally posted by nigelrivett

select *
from tbl t
where pos = (select max(pos) from tbl t2 where t.cust = t2.cust and t.orderno = t2.orderno)

select * from
(
select *, seq = rownumber() over (partition by cust, orderno order by pos desc)
from tbl
) a
where seq = 1

oops = v2005 - not supported - use the first one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

It is supported in 2005.
ROW_NUMBER() ~~rownumber()~~

No, you're never too old to Yak'n'Roll if you're too young to die.

Oops again - has it been that long?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 05:01:42

quote:
Originally posted by nigelrivett

quote:
Originally posted by webfred

quote:
Originally posted by nigelrivett

select *
from tbl t
where pos = (select max(pos) from tbl t2 where t.cust = t2.cust and t.orderno = t2.orderno)

select * from
(
select *, seq = rownumber() over (partition by cust, orderno order by pos desc)
from tbl
) a
where seq = 1

oops = v2005 - not supported - use the first one.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

It is supported in 2005.
ROW_NUMBER() ~~rownumber()~~

No, you're never too old to Yak'n'Roll if you're too young to die.

Oops again - has it been that long?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

its supported from 2005 onwards with compatibilty level 90

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-10-07 : 05:16:44

thanks to all
it is work

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 05:33:48

welcome