How do I get the all columns of a row for the row with the highest value of a column

Question

Post reply

How do I get the all columns of a row for the row with the highest value of a column

groads2

SSC Veteran

Points: 254
More actions
July 15, 2012 at 8:43 am

#259468

I have a table that has many columns. I want to return all the columns of the rows with the greatest ID for the unit id.
example
ID, unit
1,33
2,33
3,25
4,55
5,55
the result I am looking for is:
2,33
3,25
5,55
I hope I explained it well.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you desire ?

CREATE TABLE #T(ID INT, Unit INT)

INSERT INTO #t

SELECT 1,33 UNION ALL

SELECT 2,33 UNION ALL

SELECT 3,25 UNION ALL

SELECT 4,55 UNION ALL

SELECT 5,55

SELECT MAX(ID)AS 'id', Unit FROM #T group by unit

Results:

Id Unit

3 25

2 33

5 55

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SELECT <other columns> FROM

(SELECT

<other columns>,

ROW_NUMBER() OVER (PARTION BY unit ORDER BY ID DESC) AS RowNo

FROM <Some Table>

)sub

WHERE RowNo = 1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

indraprakash72 SSC Journeyman Points: 84 More actions · Answer 3

WITH CTE AS

(SELECT *, row_number() over (partition by unit order by id desc) as rowno

from tablename

)

select * from CTE where rowno=1;

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

-- Sample code

CREATE TABLE #T(ID INT, Unit INT)

INSERT INTO #t

SELECT 1,33 UNION ALL

SELECT 2,33 UNION ALL

SELECT 3,25 UNION ALL

SELECT 4,55 UNION ALL

SELECT 5,55

-- Query

SELECT MAX(ID) ID, MAX(Unit) Unit

FROM #t

GROUP BY Unit

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

groads2 SSC Veteran Points: 254 More actions · Answer 5

I hope I am repling correctly. Im an old dog trying to learn new tricks. The replies did work on my first problem. The second problem involved more columns with the same concept. I decided not to post that one but to forge on trying to figure some things out. With all the replies I got fixed up on the other one. Thank you very much for your help. Hope I can help someone in the future.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

groads2 (7/16/2012)
I hope I am repling correctly. Im an old dog trying to learn new tricks. The replies did work on my first problem. The second problem involved more columns with the same concept. I decided not to post that one but to forge on trying to figure some things out. With all the replies I got fixed up on the other one. Thank you very much for your help. Hope I can help someone in the future.

You can help now. Post your solution so that others can learn if they read this thread. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)