Select Value By Order

[ooch1]

This relates to an earlier transposing query, but if anyone know how i can select an inremental value by order then i.e if there are ten values how could i select the 6th etc.

OOch

 

[PHV]

Take a look at TOP:
SELECT TOP 1 field_list
FROM (SELECT TOP 6 field_list FROM table_list
ORDER BY sort_list ASC)
ORDER BY sort_list DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PH - I think we could be ionto a winner here but i need more help as my sql isn't brilliant as i normally just use query builder in access.
If you could provide and example of how i get a new column to look at the 2nd value, then this should be enough to get me going with the rest.

I appreciate this help!!

The current SQL is as follows and is essentially just selecting 3 rows:

SELECT Gas_Hull_PortAnlys_IMPORT.SiteRefNum, Gas_Hull_PortAnlys_IMPORT.[Site name], Gas_Hull_PortAnlys_IMPORT.MeterPointRef
FROM Gas_Hull_PortAnlys_IMPORT
GROUP BY Gas_Hull_PortAnlys_IMPORT.SiteRefNum, Gas_Hull_PortAnlys_IMPORT.[Site name], Gas_Hull_PortAnlys_IMPORT.MeterPointRef;

 

[PHV]

Can you please be more specific on your issue ?
What is the expected result with what ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PH- I would just like to have a column for all the 1st values retirved, another for the 2nd etc etc.

My previous post may put the end game into more context though:

http://www.tek-tips.com/viewthread.cfm?qid=931066

OOch

 

[PHV]

Why would you denormalyze your DB ?
Anyway I don't see solution in pure SQL.
Take a look at the recordset animal in VBA.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

ooch1,
I have replied in your other thread with the same basic question. Please keep to a single thread.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.