sql question

[Guy Cohen]

Hi all
Lets say I have columns: c1 d1 c2 d2 c3 d3
Cx are integers
Dx are dates

I want to select one from the C columns where their D column is the latest
e.g.

c1 d1 c2 d2 c3 d3
1, 01/jan/2008 ,5, 02/jan/2008 ,10, 10/jan/2008
4, 01/jan/2008 ,NULL,NULL,NULL,NULL

Select from table t should return:
1
4

Please advise.
TIA
Guy Cohen

 

[rowe_newsgroups]

Hi all
Lets say I have columns: c1 d1 c2 d2 c3 d3
Cx are integers
Dx are dates

I want to select one from the C columns where their D column is the latest
e.g.

c1 d1 c2 d2 c3 d3
1, 01/jan/2008 ,5, 02/jan/2008 ,10, 10/jan/2008
4, 01/jan/2008 ,NULL,NULL,NULL,NULL

Select from table t should return:
1
4

Please advise.
TIA
Guy Cohen

How is this a Visual Basic.NET question? You should probably go to
comp.databases.ms-sqlserver or one of the other SQL newsgroups.

Thanks,

Seth Rowe [MVP]

 

[Adamz5]

Naughty boy!!!!

Normalise your data!!!!

so your table would have a the following 4 fields

Unique Key / ColKey / C1 / d1

Example
1 1 1 01/jan/2008
2 1 5 02/jan/2008
3 1 10 10/Jan/2008

4 2 4 01/jan/2008
5 2 NULL NULL
6 2 NULL NULL

them simply qry the above table as below:

select f.colkey, f.col1,f.col2
from
(select colkey, min(col2) as col2
from Table where col1 is not null group by colkey
) as x inner join Table f on f.colkey = x.colkey and f.col2 = x.col2

if you have sql 2005 you can easily use CTE

Hope this helps

Regards

Adam Issat

 

[Adamz5]

Naughty boy!!!!

Normalise your data!!!!

so your table would have a the following 4 fields

Unique Key / ColKey  /  C1  / d1

Example
1       1       1       01/jan/2008
2       1       5       02/jan/2008
3       1       10           10/Jan/2008

4       2       4       01/jan/2008
5       2       NULL    NULL
6       2       NULL    NULL

them simply qry the above table as below:

select f.colkey, f.col1,f.col2
from
(select colkey, min(col2) as col2
from Table where col1 is not null group by colkey
) as x inner join Table f on f.colkey = x.colkey and f.col2 = x.col2

if you have sql 2005 you can easily use CTE

Hope this helps

Regards

Adam Issat

Please replace c1 and d1 with columnnames col1 col2 -thanks adam