| View previous topic :: View next topic |
| Author |
Message |
KM
Beginner
Joined: 22 Jul 2004
Posts: 18
Topics: 8
|
 Posted: Tue Sep 21, 2004 11:06 am Post subject: Find first and last row... |
 |
|
It seems simple enough.
Create a SPUFI that can return (and eventually update) the first and the last row for a given criteria.
I've looked for somekind of MIN / MAX feature, but haven't been able to locate any.
Any suggestions ? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Sep 21, 2004 11:32 am Post subject: |
|
|
KM,
Try the following sql.
| Code: |
UPDATE TABLE
SET COL1 = 'MIN UPDATE VALUE'
WHERE COL2 = (SELECT MIN(COL2) FROM TABLE)
;
UPDATE TABLE
SET COL1 = 'MAX UPDATE VALUE'
WHERE COL2 = (SELECT MAX(COL2) FROM TABLE)
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
KM
Beginner
Joined: 22 Jul 2004
Posts: 18
Topics: 8
|
| Posted: Tue Sep 21, 2004 1:32 pm Post subject: |
|
|
Quick reply. Much appreciated !
It gives me an idea, yes, but before any update can take place I need to be able to pull the first and the last row:
123 AAA
123 BBB
123 CCC
123 DDD
123 EEE
123 FFF
How do I pull just AAA and FFF ?. Bear in mind that there could be more than six rows.
After that, I will update these rows.... |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Sep 21, 2004 1:40 pm Post subject: |
|
|
Km,
| Code: |
COL1 COL2
123 AAA
123 BBB
123 CCC
123 DDD
123 EEE
123 FFF
|
The following sql will give you 'AAA'
| Code: |
SELECT MIN(COL2)
FROM TABLE
;
|
The following sql will give you 'FFF'
| Code: |
SELECT MAX(COL2)
FROM TABLE
;
|
If you want the other column information then you need to change the sql to the following
| Code: |
SELECT *
FROM TABLE
where COL2 = (SELECT MIN(COL2)
FROM TABLE)
;
SELECT *
FROM TABLE
where COL2 = (SELECT MAX(COL2)
FROM TABLE)
;
|
Hope this helps...
Cheers
kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
KM
Beginner
Joined: 22 Jul 2004
Posts: 18
Topics: 8
|
| Posted: Tue Sep 21, 2004 1:57 pm Post subject: |
|
|
Excellent.
Strange how two lines of code can make you happy. You made my day !
Thanks again ! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
