group by? - 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

group by?

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-05-16 : 17:22:12

Hi,

I've got the following Table:




 dataid   Name   Vernum   valstr  
 5088726   cvp.docx   2   Internal  
 5088726   cvp.docx   3   Personal  
 5088726   cvp.docx   1   Strictly Confidential

I want as result one row. I need the value of column valstr where the value of the column is the highest.

I my example the result must be personal.

How Can I do it?

I tried to work with group by but it's not correct...i hope you will have a hint for me....

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-16 : 17:47:05

Look at the PIVOT operator. BOL has details.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2013-05-16 : 19:08:01

Hi,

thanks for your help...I know pivot from excel..but i can't see how to work with it in my case..i checked some sql samples and I can use table entries as headers...but, it is not whar I want...is there not another way or do I miss something?

Kinf regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 23:58:52

[code]
SELECT dataid, Name, Vernum, valstr
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY dataid ORDER BY Vernum DESC) AS rn
FROM YourTable
)t
WHERE rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources