SQL Query help

Question

SQL Query help

sqlcool

SSC Eights!

Points: 967
More actions
January 26, 2009 at 5:51 pm

#199243

Hi there
Could u pls help to achieve this. This is not simple as u guys think pls send me the query instead os telling its simple
i need top version on each id as a result
ORIGINAL DATA
id version text age sex
1 1 aa 1 M
1 2 bb 2 M
13 cc 1 M
21 dd 3 M
31 ee 4 M
32 ff 1 F
RESULT
IDVERSION text age sex
13 cc 1 M
21 dd 3 M
32 ff 1 F
Cheers

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sqlcool (1/26/2009)
Hi there
Could u pls help to achieve this.
i need top version on each id as a result
ORIGINAL DATA
id version
1 1
1 2
13
21
31
32
RESULT
IDVERSION
13
21
32
Cheers

Be more than glad to help, once you show us what you have tried so far. Has something to do with fish...

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 2

Two possible answers, both will work:

TOP

ROW_NUMBER

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Lynn Pettis (1/26/2009)
Be more than glad to help, once you show us what you have tried so far. Has something to do with fish...

Mmm, learning you are... 🙂

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

January 26, 2009 at 7:12 pm

#932952

hmmm, yes. Simple ones too easy to just give away.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

Grant Fritchey (1/26/2009)
Two possible answers, both will work:
TOP
ROW_NUMBER

Simple MAX would do it...

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Think, actually a GROUP BY with MAX in SELECT, hmmm

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 7

Jeff Moden (1/26/2009)
Grant Fritchey (1/26/2009)
Two possible answers, both will work:
TOP
ROW_NUMBER
Simple MAX would do it...

Simple isn't nearly as much fun. Small data set, ROW_NUMBER may perform better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

sqlcool (1/26/2009)
Hi there
Could u pls help to achieve this.
i need top version on each id as a result
ORIGINAL DATA
id version
1 1
1 2
13
21
31
32
RESULT
IDVERSION
13
21
32
Cheers

select

from

group by

Nothing fancy.