sql statement

[LaxRoth]

Hi, i've got a question using SQL. I have a table which has following attributes: beginn, end, ressource. all are numbers.
i need a statement to select all records on one ressource that have the latest end of all records on the same ressource. anyone a suggestion? do i need a join or something?





thx in forward,

LaxRoth

[dinesh123]

Try This


Select * from [Table] Where UnitPrice In ( Select Max(End) from [Table] ) And ressource = 1

[LaxRoth]

thx, but the problem is that i haven't got the ressource =... i need all records!

[dinesh123]

Can u put some data here and the output you are expecting

[LaxRoth]

hello, here is some data.

pk ressource end
== ======= ===
1 1000 20040501_000000
2 1000 20040503_000000
3 1000 20040617_083200
4 1005 20030101_105055
5 1005 20030823_000000
6 1005 20040505_000000
7 1006 20031201_080000
8 1008 20021231_000000
9 1008 20040825_123746
10 1008 20040826_080000

my date of which i want the last record per ressource before is: 20040502_000000

the records in the table are ordered randomly.

date format is: yyyymmdd_hhmmss, so i can select with 'order by end' to have it in a timeline.

i want to select the following records:
1, 5, 7, 8

u see i want to every ressource in the table the corresponding record with the backward nearest date to 20040502_000000.

thx in advance, LaxRoth

[DTMB]

Hi LaxRoth, I don't know if you can do with only sql language what you want but I'll write you this statement that permit to select the max records with the same "ressource".

Select * from [Table] WHERE [End] IN (Select Max([End]) from [Table] GROUP BY ressource)

I hope that you can use it.
Bye