Trying to GET column which correspond to Max 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

Trying to GET column which correspond to Max VALUE

Author

Topic

PokerGuru
Starting Member

2 Posts

Posted - 2011-10-27 : 10:48:42

Hi,

The table contains the following:
APAR_ID TAPE_SERIES N:REQUEST_ID APAR_ORDER_NO
ZA04450 TEL999 N R00696 3
ZA04452 TEL999 N R00696 1
ZA04451 TEL999 N R00696 2

I am trying to return the APAR ID which corresponds to the MAX APAR_ORDER_NO, so that would be ZA04450
Here is my query:
SELECT MAX(APAR_ORDER_NO),
APAR_ID
INTO
:max_apar_ord_no,
:apar_id_db
FROM STCUTSTG.CUT_APAR_TAPE
WHERE REQUEST_ID = 'R00696'
GROUP BY APAR_ID
I am getting the following error:
Additional rows of data remain. One row returned.

Any hints as to what's wrong?
Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 11:14:36

Depending on the requirement maybe one of these

SELECT *
FROM STCUTSTG.CUT_APAR_TAPE
WHERE REQUEST_ID = 'R00696'
and APAR_ORDER_NO = (select top 1 APAR_ORDER_NO from STCUTSTG.CUT_APAR_TAPE where REQUEST_ID = 'R00696' order by APAR_ORDER_NO desc)

SELECT top 1 *
FROM STCUTSTG.CUT_APAR_TAPE
WHERE REQUEST_ID = 'R00696'
order by APAR_ORDER_NO desc

;with cte as
(
select *, seq = row_number() over (partition by REQUEST_ID order by APAR_ORDER_NO desc)
from STCUTSTG.CUT_APAR_TAPE
)
select *
from cte
where REQUEST_ID = 'R00696'
and seq = 1

==========================================
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-27 : 12:03:20

[code]
SELECT t1.*
FROM (SELECT DISTINCT REQUEST_ID FROM Table)t
CROSS APPLY (SELECT TOP 1 *
FROM table
WHERE REQUEST_ID = t.REQUEST_ID
ORDER BY APAR_ORDER_NO DESC) t1
[/code]

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

PokerGuru
Starting Member

2 Posts

Posted - 2011-10-27 : 12:33:55

Thanks guys,
the following worked for me
"SELECT APAR_ID INTO :apar_id_db",
" FROM "||db2loc||db2owner||"CUT_APAR_TAPE",
" WHERE REQUEST_ID = '"||STRIP(rqstid)||"'",
" AND APAR_ORDER_NO = (SELECT MAX(APAR_ORDER_NO) FROM",
" "||db2loc||db2owner||"CUT_APAR_TAPE",
" WHERE REQUEST_ID = '"||STRIP(rqstid)||"')"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 12:39:32

quote:
Originally posted by PokerGuru

Thanks guys,
the following worked for me
"SELECT APAR_ID INTO :apar_id_db",
" FROM "||db2loc||db2owner||"CUT_APAR_TAPE",
" WHERE REQUEST_ID = '"||STRIP(rqstid)||"'",
" AND APAR_ORDER_NO = (SELECT MAX(APAR_ORDER_NO) FROM",
" "||db2loc||db2owner||"CUT_APAR_TAPE",
" WHERE REQUEST_ID = '"||STRIP(rqstid)||"')"

Looks like you're using Oracle
you should have specified it
This is actually MS SQL Server forum

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources