SQL help again

[Joe_Fox]

Hi,
Who can solve this problem?

I have a table of data that has four columns, one of which is a version number:

e.g.

Item  | Ref Number | Date     | Version
Hat   | 23         | 16/10/08 | 1
Hat   | 23         | 18/02/09 | 2
Socks | 53         | 18/02/09 | 1  
Socks | 53         | 20/03/09 | 2
Socks | 53         | 30/07/09 | 3
Pants | 76         | 17/11/08 | 1
Pants | 76         | 18/12/08 | 2
Pants | 76         | 18/03/09 | 3

I need to write a query that only returns the latest version of each item together with just the Item and Ref Number columns.

So, the result should be:

Hat   | 23 | 2
Socks | 53 | 3
Pants | 76 | 3

Can someone help? Prize to the person who gets the write answer first.

 

[Matt_B]

SELECT     item, ref, version
FROM         tbltest AS t
WHERE     (version IN
                          (SELECT     MAX(version) AS Expr1
                            FROM          tbltest
                            WHERE      (t.ref = ref)
                            GROUP BY ref))

Requires that the ref field is the same per product, so all socks must be 53 etc.

 

[Darth Vodka]

select item,[ref number],max(version) as Version
from tbltest
group by item,[ref number]

 

[Darth Vodka]

don't mention it joe

 

[Macky]

don't mention it joe

Bad form, isn't it?
I think we should just refuse to help in future

 

[Darth Vodka]

Bad form, isn't it?
I think we should just refuse to help in future

yeah nobody answer his fecking camera q

 

[Matt_B]

yeah nobody answer his fecking camera q

Oops, read that one first :icon_redf

 

[bocadillo]

Oops, read that one first :icon_redf

You could always ask a moderator to delete it for you.

 

[Joe_Fox]

I'm still in the process of testing both your answers.
The prize will come in good time. Patience, patience.

 

[Darth Vodka]

I'm still in the process of testing both your answers.
The prize will come in good time. Patience, patience.

don't mention it

 

[Matt_B]

They both work, I tested them both. I think DV's might be more efficient though.

 

[Joe_Fox]

Darth, who is your favourite actress, Beighton, same question to you?
(p.s. If either of you prefer 'actors' then say who your favourite actor is instead)

 

[Joe_Fox]

Well you missed your chance of a present there (both of you).

Any way, I need some more help now.

I've written some SQL that joins three tables.

Here is an example:

select a.po,
         b.bo,
         c.mo

from ano a, bno b, cno, c

where a.po = b.po and b.bo = c.bo

This returns the rows I expect but I'm only really interested in the data in column c.mo.
An example of what I get back is this:

543    6   Black
432    6   Black
732    3   White
991    1   Green
191    1   Green
291    1   Green
452    6   Black
462    6   Black
472    6   Black
482    6   Black

but all I want to see is a distinct value, no duplicates:

543    6   Black
732    3   White
991    1   Green

As the data in the first two columns is not important, is there a way to just show the distinct values returned in the third column?

Thanks,
Joe

 

[Joe_Fox]

Damn, I sussed it myself (although writing the problem out actually made me realise what I needed to do)

Here is what was needed:

select distinct c.mo

from ano a, bno b, cno, c

where a.po = b.po and b.bo = c.bo

simple as that leeeeeeeeeeeeeeeeeeeeeeeeek

 

[Darth Vodka]

joins in the where clause....old skool