translating Access sql to mysql

[Guest]

Hello,
I am translating an Access query to mysql and cannot find a mysql equivalent
to the functions "First()" and "Last()". Is there a mysql equivalent? Or do
you know an alternate way to get the first and last records within the group?
This is my Access sql:
SELECT TransactionDetails.TransactionID, First(TransactionDetails.Q) AS
FirstOfQ, Min(TransactionDetails.timeIN) AS MinOftimeIN,
Last(TransactionDetails.Q) AS LastOfQ, Max(TransactionDetails.timeIN) AS
MaxOftimeIN
FROM TransactionDetails
GROUP BY TransactionDetails.TransactionID;

Thanks so much for your help!

 

[Alex Dybenko]

Hi,
you can use "Select top 1 ..." with proper order

 

[Guest]

Select Top 1 would not work because I need to select both the first and last
record within a query, I cannot depend on sort order for this. Additionally,
my data looks like this:

group time variable
1 1:30 n
1 1:45 s
1 1:50 a
2 3:00 n
2 5:00 a

Within each group (in the example 1 or 2) I need to select the earliest and
latest time and variable corresponding to them. In group 1, earliest would be
1:30 and n and latest would be 1:50 and a.

Any other suggestions?
I really appreciate the advise, I have looked everywhere and have not found
an equivalent to First() and Last() in mysql.

Marta

 

[Alex Dybenko]

Don't understand why Select Top 1 would not work. try the folowing:

Select (Select Top 1 Time from MyTable Where Group=1 order by time) as
earliest, (Select Top 1 Time from MyTable Where Group=1 order by time DESC)
as latest, <here continues mail sql>

well, maybe mysql has some limitations, but in sql server this works fine