Anyone help me with a SQL select statemen? 1

[GriffMG]

Hi All,

I have a simple table that I need to extract some records from...

madeby  clientdrg  daterecd  drgno
A       1234       20080101    800
A       1234       20080102    810
A       1234       20080103    808
A       1234       20080103    809
A       5678       20080101    799
A       5678       20080102    904
A       5678       20080104    989
A       5678       20080105    905
A       5678       20080105    915

And what I need is...

madeby  clientdrg  daterecd  drgno
A       1234       20080103    809
A       5678       20080105    915

What I'm trying is to extract the most recent record for each drawing and where there are two on the same date, take the one with the biggest drgno.

I ought to be able to do this with a subquery, but I can't work it out. I tried using a 'TOP 1', 'ORDER BY' subquery but the OLEDB I'm using doesn't support that for correlated subqueries. I tried using max() but I need the max of two fields and I just can't get my head around it.

Any help would be gratefully received.

Martin

This is the closest I have got:

CODE
SELECT * FROM myTable a WHERE drgno in (SELECT MAX(drgno) from myTable b WHERE a.madeby=b.madeby AND a.clientdrg=b.clientdrg) ORDER BY madeby,clientdrg
But it ignores the daterecd bit
Regards

Griff
Keep ing


Regards

Griff
Keep [Smile]ing

 

[gmmastros]

This should work.

Declare @Temp Table(MadeBy VarChar(20), ClientDrg Int, DateRecd DateTime, drgno int)

Insert Into @Temp Values('A',1234,'20080101',800)
Insert Into @Temp Values('A',1234,'20080102',810)
Insert Into @Temp Values('A',1234,'20080103',808)
Insert Into @Temp Values('A',1234,'20080103',809)
Insert Into @Temp Values('A',5678,'20080101',799)
Insert Into @Temp Values('A',5678,'20080102',904)
Insert Into @Temp Values('A',5678,'20080104',989)
Insert Into @Temp Values('A',5678,'20080105',905)
Insert Into @Temp Values('A',5678,'20080105',915)

Select	T.MadeBy,
        T.ClientDrg,
        T.DateRecd,
        Max(drgno) As drgno
From    @Temp T
        Inner Join (
           Select MadeBy, ClientDrg, Max(DateRecd) As MaxDateRecd
           From   @Temp
           Group BY MadeBy, ClientDrg
           ) As A
           On T.MadeBy = A.MadeBy
           And T.ClientDrg = A.ClientDrg
           And T.DateRecd = A.MaxDateRecd
Group BY T.MadeBy, T.ClientDrg, T.DateRecd

Note that I used a table variable to dummy up some data to test on. You'll need to change the query to use your table.

The trick here is...

First, write a query that gets the max date, then use this as a derived table to join back to the original table where you then get the max drgno.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

George, If this is not a FAQ it might make a good one as I have to remind myself of the trick.

djj

 

[Ach005ki]

George, I agree with djj as derived tables are an especially useful tool.

George, I think you deserve a star for your efforts!

Mark, somewhere near Blackburn Lancs!

 

[GriffMG]

Thank you all, that has helped me get nearly there!

Martin

Regards

Griff
Keep [Smile]ing