Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Hi all,
 
I want to find out maximum value in a row. So, Iam using unpivot to find out the max value but it is giving an error. Can any one please help me. Thanks in advance.
 
CREATE TABLE MAXVALUE
(
ID INT IDENTITY,
A INT,
B INT,
C INT
)
 
this is myquery :
 
SELECT MAX(VAL) AS MAXVALUE_ROW FROM
    (SELECT *FROM MAXVALUE UNPIVOT
    (Id for [max(val)] IN
    ([A],[B],[C])
    ))
 

Error:
 
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Posted 27-Dec-12 18:51pm
Edited 28-Dec-12 0:58am
v4
Comments
Sandeep Mewara at 28-Dec-12 4:24am
   
What error? Please share.
bapu_reddy at 28-Dec-12 4:54am
   
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ')'.
Aarti Meswania at 28-Dec-12 5:17am
   
can you give some input output records?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

hi , sorry to say i don't have time to give solution for your post , i have one sample code .
please have a look on this
 
UNPIVOT:
-------
Used to convert columns into rows
 
Create table tbl_stdmarksdata  (studentname nvarchar(100), I int, II int, III int, IV int, V int)
go
 
Insert into tbl_stdmarksdata (studentname,I,II,III,IV,V)
Select 'Vivek Johari',30,20,35, 40, 45
Union All
Select 'Chandra Singh',30,20,35,44, 80
Union All
Select 'Avinash Dubey',30,25,35,20, 39
Union All
Select 'Pankaj Kumar',33,29,30, 60, 50
go
 
select * from tbl_stdmarksdata
go
select studentname,Marks,Grade
from tbl_stdmarksdata
unpivot
(Marks for Grade in (I,II,III,IV,V) ) as tblunpvt
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

try this...
 
select id,COL,VAL from
(
	SELECT Row_Number() over(order by val desc) as srno, id,COL,VAL
	FROM
	  (SELECT * FROM MAXVALUE ) as p
	UNPIVOT
	  (VAL for COL IN ([A],[B],[C])) as b
) as t where srno=1
 
Happy Coding!
Smile | :)
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 335
1 Nirav Prabtani 252
2 Richard Deeming 215
3 CHill60 170
4 _Amy 145
0 OriginalGriff 8,104
1 Sergey Alexandrovich Kryukov 7,045
2 Maciej Los 4,039
3 Peter Leow 3,738
4 CHill60 2,912


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100