compare 2 values in same solumn

I am trying to compare the last two values in the same column of a
table. First of all, I have a column titled Row_Index that uses an
index which starts at 1 and increments by 1. What I am trying to do
is compare the values in the column 'Shift_Date' for the maximum value
of Row_Index and the (maximum value - 1) of Row_Index. I've been
trying to declare two strings, setting each string equal to one of my
values, and then comparing the strings. But this doesn't seam to be
working right. I'm not sure I am even declaring the strings
correctly. Does anyone know how I can do this. A sample of what one
of my values looks like in Shift_Date is ' 6/25/2007'.

Re: compare 2 values in same solumn

What exactly is the datatype of Shift_Date? What action are you
trying to take based on the comparison?

If you want to compare them inside a SELECT this might get you
started.

SELECT A.ShiftDate as LastShiftDate,
B.ShiftDate as NextToLastShiftDate
FROM (SELECT * FROM TheTable
WHERE Row_Index =
(SELECT MAX(Row_Index) FROM TheTable) as A
CROSS JOIN
(SELECT * FROM TheTable
WHERE Row_Index =
(SELECT MAX(Row_Index)-1 FROM TheTable) as B

Roy Harvey
Beacon Falls, CT

On Mon, 25 Jun 2007 15:44:07 -0000, mcolson
wrote:

>I am trying to compare the last two values in the same column of a
>table. First of all, I have a column titled Row_Index that uses an
>index which starts at 1 and increments by 1. What I am trying to do
>is compare the values in the column 'Shift_Date' for the maximum value
>of Row_Index and the (maximum value - 1) of Row_Index. I've been
>trying to declare two strings, setting each string equal to one of my
>values, and then comparing the strings. But this doesn't seam to be
>working right. I'm not sure I am even declaring the strings
>correctly. Does anyone know how I can do this. A sample of what one
>of my values looks like in Shift_Date is ' 6/25/2007'.

Re: compare 2 values in same solumn

On Jun 25, 11:01 am, Roy Harvey wrote:
> What exactly is the datatype of Shift_Date? What action are you
> trying to take based on the comparison?
>
> If you want to compare them inside a SELECT this might get you
> started.
>
> SELECT A.ShiftDate as LastShiftDate,
> B.ShiftDate as NextToLastShiftDate
> FROM (SELECT * FROM TheTable
> WHERE Row_Index =
> (SELECT MAX(Row_Index) FROM TheTable) as A
> CROSS JOIN
> (SELECT * FROM TheTable
> WHERE Row_Index =
> (SELECT MAX(Row_Index)-1 FROM TheTable) as B
>
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 25 Jun 2007 15:44:07 -0000, mcolson
> wrote:
>
> >I am trying to compare the last two values in the same column of a
> >table. First of all, I have a column titled Row_Index that uses an
> >index which starts at 1 and increments by 1. What I am trying to do
> >is compare the values in the column 'Shift_Date' for the maximum value
> >of Row_Index and the (maximum value - 1) of Row_Index. I've been
> >trying to declare two strings, setting each string equal to one of my
> >values, and then comparing the strings. But this doesn't seam to be
> >working right. I'm not sure I am even declaring the strings
> >correctly. Does anyone know how I can do this. A sample of what one
> >of my values looks like in Shift_Date is ' 6/25/2007'.

When using the 'Last' function, do you have to have the columns sorted
by when they were entered?

Re: compare 2 values in same solumn

>When using the 'Last' function, do you have to have the columns sorted
>by when they were entered?

What 'Last' function? I did not use any such function, and have not
heard of any such function.

MAX() is unrelated to order. SQL tables are not ordered anyway, any
order you need must be built into the data so that you can use ORDER
BY to control the order of the results when querying.

TOP is dependent on order (contolled using ORDER BY), but I did not
use TOP.

Roy Harvey
Beacon Falls, CT

On Mon, 25 Jun 2007 17:29:08 -0000, mcolson
wrote:

>On Jun 25, 11:01 am, Roy Harvey wrote:
>> What exactly is the datatype of Shift_Date? What action are you
>> trying to take based on the comparison?
>>
>> If you want to compare them inside a SELECT this might get you
>> started.
>>
>> SELECT A.ShiftDate as LastShiftDate,
>> B.ShiftDate as NextToLastShiftDate
>> FROM (SELECT * FROM TheTable
>> WHERE Row_Index =
>> (SELECT MAX(Row_Index) FROM TheTable) as A
>> CROSS JOIN
>> (SELECT * FROM TheTable
>> WHERE Row_Index =
>> (SELECT MAX(Row_Index)-1 FROM TheTable) as B
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Mon, 25 Jun 2007 15:44:07 -0000, mcolson
>> wrote:
>>
>> >I am trying to compare the last two values in the same column of a
>> >table. First of all, I have a column titled Row_Index that uses an
>> >index which starts at 1 and increments by 1. What I am trying to do
>> >is compare the values in the column 'Shift_Date' for the maximum value
>> >of Row_Index and the (maximum value - 1) of Row_Index. I've been
>> >trying to declare two strings, setting each string equal to one of my
>> >values, and then comparing the strings. But this doesn't seam to be
>> >working right. I'm not sure I am even declaring the strings
>> >correctly. Does anyone know how I can do this. A sample of what one
>> >of my values looks like in Shift_Date is ' 6/25/2007'.
>
>When using the 'Last' function, do you have to have the columns sorted
>by when they were entered?

Re: compare 2 values in same solumn

> >When using the 'Last' function, do you have to have the columns sorted
>>by when they were entered?
>
> What 'Last' function? I did not use any such function, and have not
> heard of any such function.

FIRST and LAST are non-relational aggregate functions in Access. In the
latest version of Access, these return the first and last values of an
ordered set. However, the returned values are arbitrary when the result is
unordered and I don't think the result is well-defined in many cases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Roy Harvey" wrote in message
news:hh008312fs128vcm5aqt5h5ichnpsb5g3c@4ax.com...
> >When using the 'Last' function, do you have to have the columns sorted
>>by when they were entered?
>
> What 'Last' function? I did not use any such function, and have not
> heard of any such function.
>
> MAX() is unrelated to order. SQL tables are not ordered anyway, any
> order you need must be built into the data so that you can use ORDER
> BY to control the order of the results when querying.
>
> TOP is dependent on order (contolled using ORDER BY), but I did not
> use TOP.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 25 Jun 2007 17:29:08 -0000, mcolson
> wrote:
>
>>On Jun 25, 11:01 am, Roy Harvey wrote:
>>> What exactly is the datatype of Shift_Date? What action are you
>>> trying to take based on the comparison?
>>>
>>> If you want to compare them inside a SELECT this might get you
>>> started.
>>>
>>> SELECT A.ShiftDate as LastShiftDate,
>>> B.ShiftDate as NextToLastShiftDate
>>> FROM (SELECT * FROM TheTable
>>> WHERE Row_Index =
>>> (SELECT MAX(Row_Index) FROM TheTable) as A
>>> CROSS JOIN
>>> (SELECT * FROM TheTable
>>> WHERE Row_Index =
>>> (SELECT MAX(Row_Index)-1 FROM TheTable) as B
>>>
>>> Roy Harvey
>>> Beacon Falls, CT
>>>
>>> On Mon, 25 Jun 2007 15:44:07 -0000, mcolson
>>> wrote:
>>>
>>> >I am trying to compare the last two values in the same column of a
>>> >table. First of all, I have a column titled Row_Index that uses an
>>> >index which starts at 1 and increments by 1. What I am trying to do
>>> >is compare the values in the column 'Shift_Date' for the maximum value
>>> >of Row_Index and the (maximum value - 1) of Row_Index. I've been
>>> >trying to declare two strings, setting each string equal to one of my
>>> >values, and then comparing the strings. But this doesn't seam to be
>>> >working right. I'm not sure I am even declaring the strings
>>> >correctly. Does anyone know how I can do this. A sample of what one
>>> >of my values looks like in Shift_Date is ' 6/25/2007'.
>>
>>When using the 'Last' function, do you have to have the columns sorted
>>by when they were entered?

Re: compare 2 values in same solumn

On Tue, 26 Jun 2007 12:42:49 GMT, "Dan Guzman"
wrote:

>
>> >When using the 'Last' function, do you have to have the columns sorted
>>>by when they were entered?
>>
>> What 'Last' function? I did not use any such function, and have not
>> heard of any such function.
>
>FIRST and LAST are non-relational aggregate functions in Access. In the
>latest version of Access, these return the first and last values of an
>ordered set. However, the returned values are arbitrary when the result is
>unordered and I don't think the result is well-defined in many cases.

Thanks for clueing me in, Dan!

Roy