Get only Max of each distinct?

#1

Hello,

I have a table

ItemID Version

12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0
15 2.1

How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?

Like this:

ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

Any help would be appreciated.

Thanks

#2

sunilkes (AT) gmail (DOT) com wrote:

Quote:

I have a table

ItemID Version

12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0
I assume 5.0 is a typo for 2.0
15 2.1

How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?

Like this:

ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

This smells like homework. Look up MAX() and GROUP BY.

#3

On Nov 7, 10:10 am, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:

Quote:

sunil... (AT) gmail (DOT) com wrote:
I have a table

ItemID Version

12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0

I assume 5.0 is a typo for 2.0

Got it, actually it was pretty simple, was trying it incorrectly

earlier !

SELECT MAX(Version_Number) AS Maxim, ItemId
FROM tblItems
GROUP BY ItemId

Thanks

Quote:

15 2.1

How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?

Like this:

ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

This smells like homework. Look up MAX() and GROUP BY.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Get only Max of each distinct?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Get only Max of each distinct? - 11-07-2007 , 03:49 AM

Re: Get only Max of each distinct? - 11-07-2007 , 04:10 AM

Re: Get only Max of each distinct? - 11-07-2007 , 05:35 AM