-
June 18th, 2004, 09:35 AM
#1
sql statement
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
-
June 22nd, 2004, 04:49 AM
#2
Try This
Select * from [Table] Where UnitPrice In ( Select Max(End) from [Table] ) And ressource = 1
-
June 22nd, 2004, 07:30 AM
#3
thx, but the problem is that i haven't got the ressource =... i need all records!
-
June 22nd, 2004, 10:51 PM
#4
Can u put some data here and the output you are expecting
-
June 23rd, 2004, 01:47 AM
#5
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
Last edited by LaxRoth; June 23rd, 2004 at 01:51 AM.
-
June 24th, 2004, 06:52 AM
#6
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|