Choose middle or different Value than First or Last in Query

[tortelvis]

I have a query where I'm selecting the First value of a text field. Is there anyway in the query to choose either the middle value, or a specified value?

In my group by - I have 5 fields...
Shirt XS
Shirt S
Shirt M
Shirt L
Shirt XL
I can choose First and the query returns Shirt XS in the grouping. I can choose Last and get Shirt XL. But what if I want the 3rd value to always be returned. (or 4th for that matter).

Thanks,
Tort

 

[jebry]

Hi!

Try adding:

MyTextField Having "Shirt S"

to get the second value. and similar statements in the SQL to get the other values.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[tortelvis]

That would work for this particular instance, but my database is actually an Item Table with over 200,000 unique items.
Another Item group might be Pants XS through Pants XL. I need to always return the 3rd value (most likey "item M", but not always).

Thanks,
Tort

 

[tortelvis]

bump

 

[pbrodsky]

3rd" is not really a criterion you should be testing for. Relational databases, by definition, only guarantee a specific order when an index or a sort is specified.

What is the structure of your table?

You probably should have a field which makes the value you are looking for explicit for each item.

For this particular case, someone who knows SQL better than I do may be able to come up with something that works.

 

[Golom]

Sorta like this

Select Fld1, Fld2, ...

       (Select LAST(Fld3) From 
               (Select TOP 3 T2 Fld3 From tbl T2
                WHERE T2.Fld1 = T1.Fld1
                Order By T2.Fld3)) As ThirdValue

From tbl As T1

In essence we are pulling the TOP n (3 in this example) ; ordering them in increasing order and then picking the LAST one. That however will not give you exactly what you want because the ordering of your records will be
[tt]
Shirt L
Shirt M
Shirt S
Shirt XL
Shirt XS
[/tt]
and you will get Shirt S (i.e. the third one in the ordered list.) The ordering that you showed isn't alpha and, from the information presented, there isn't any way to guarantee that you will always see that ordering absent an ORDER BY clause.

 

[tortelvis]

Thanks!
I just embedded a new query within my query to grab the top 3, then pick the last one in my query - brilliant.

-Tort