How to take only the third row from a table ?

[ivan_will]

Hi all,

want to take only the third row from a table in the load statement - how to do that without temporary table ?

numberOfRow ID value

1 233 3

2 233 1

3 233 7

4 233 6

tnx in advance!

[johnw]

Well, as you presented it...

LOAD ... WHERE numberOfRow = 3;

I'm guessing there's more to it than that? If you don't actually have a numberOfRow field, for instance, this might work:

LOAD ... WHERE recno() = 3;

[ivan_will]

Yes, but I didn't explain the situation well .... so I have many rows (use textbetween function ... so on )

...

so where clause will igrone the others .. and do't want to make joins too !

Any other suggestion - using some function maybe ?

[johnw]

Sorry, no idea what you're asking for.

[ivan_will]

I have in one table : ID , MAKE, MODEL, YEAR, VALUE

In the field VALUE for every single ID I have 7 lines and want to take only the third one! 🙂

If use where clause this is going to ignore the other data ... don't want that ...

[johnw]

OK, now I think I understand what you want, and no, my earlier answers won't do the trick. Let me think about it. I already think I know A way, but it's not as efficient as I'd prefer.

[ivan_will]

I use

if(recno()=3, textbetween(....)) as value

but for every ID have null values and want to make the info as compact (without ***' data in it) as possible.

[johnw]

Well, here's my first attempt. It only loads the third sequence for each ID, but it DOES temporarily have to load every sequence for the ID, which is the inefficiency I mentioned. Also, since you mention the textbetween() function and I'm not sure how you're using it, I'm still not convinced I understand your problem. But here's the script, and see attached. Also, it's easier for people to help you if you attach an example like this that demonstrates your problem. If this example doesn't demonstrate your problem, perhaps you can change it so that it does.

Data:
LOAD *, if(ID<>previous(ID),1,peek(Sequence)+1) as Sequence
INLINE [
ID, Make, Model, Year, Value
1, A, A, 1971, first
1, B, B, 1972, second
1, C, C, 1973, third
1, D, D, 1974, fourth
1, E, E, 1975, fifth
1, F, F, 1976, sixth
1, G, G, 1977, seventh
2, 1, 1, 2001, A
2, 2, 2, 2002, B
2, 3, 3, 2003, C
2, 4, 4, 2004, D
2, 5, 5, 2005, E
2, 6, 6, 2006, F
2, 7, 7, 2007, G
];
INNER JOIN (Data)
LOAD 3 as Sequence
AUTOGENERATE 1
;
DROP FIELD Sequence;