General information
Category:
Coding, syntax & commands
Trying to use SELECT TOP. Have lots of records, like this:
Group1, 01/01/2001
Group1, 02/02/2001
Group1, 03/03/2001
Group1, 04/04/2001
Group2, 01/01/2001
etc
I want to get the latest three records per group. Can't get the SQL right. This doesnt't do it:-
Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf order by 1 descend GROUP BY thedbf.cGroup
Below is a little test program.
Can you help?
TIA
Cyril.
* Make sample data
Use in select('thedbf')
Create cursor thedbf (cGroup C(10), dDATE D)
For iigroup = 1 to 9
ciigroup = ltrim(str(iigroup))
For ii = 1 to 9
cii = '0'+ ltrim(str(ii))
Insert into thedbf (cGroup, dDATE) values (ciigroup, ctod(cii+'/'+cii+'/2001'))
Endfor
Endfor
*brow
* Get top 3 per cGroup
Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf order by 1 descend GROUP BY thedbf.cGroup
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only