Home > Archive > SQL Server Programming > January 2005 > select the last record: better way?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
select the last record: better way?
|
|
| Kurt Schroeder 2005-01-31, 9:00 pm |
| I have the following query and i'm selecting one record only based on the max
value of a column. is this the best way to do this? or is there a faster one?
select chartCsiID,
chartXO,
chartCol,
chartLo,
chartHi,
chartMM,
chartYYYY,
chartDOTL,
chartDOTH,
chartBS,
chartLineType
from chart where chartcsiID = 6991 and chartcol = (select max(chartcol) from
chart where chartcsiid = 6991)
| |
| raydan 2005-01-31, 9:00 pm |
| See if this helps.
The difference is that if there are more then 1 row for max(chartcol),
your query returns all rows, mine will return only one.
select top 1 chartCsiID,
> chartXO,
> chartCol,
> chartLo,
> chartHi,
> chartMM,
> chartYYYY,
> chartDOTL,
> chartDOTH,
> chartBS,
> chartLineType
> from chart where chartcsiID = 6991
order by chartcol desc
"Kurt Schroeder" <KurtSchroeder@discussions.microsoft.com> wrote in message
news:8949B035-FC13-499C-87C3-465E869FD3D0@microsoft.com...
> I have the following query and i'm selecting one record only based on the
max
> value of a column. is this the best way to do this? or is there a faster
one?
> select chartCsiID,
> chartXO,
> chartCol,
> chartLo,
> chartHi,
> chartMM,
> chartYYYY,
> chartDOTL,
> chartDOTH,
> chartBS,
> chartLineType
> from chart where chartcsiID = 6991 and chartcol = (select max(chartcol)
from
> chart where chartcsiid = 6991)
| |
| Kurt Schroeder 2005-01-31, 9:00 pm |
| I thought Top just limmited the result set, but still hit the database for
all rows?
thnaks
kes
"raydan" wrote:
> See if this helps.
> The difference is that if there are more then 1 row for max(chartcol),
> your query returns all rows, mine will return only one.
>
> select top 1 chartCsiID,
> order by chartcol desc
>
> "Kurt Schroeder" <KurtSchroeder@discussions.microsoft.com> wrote in message
> news:8949B035-FC13-499C-87C3-465E869FD3D0@microsoft.com...
> max
> one?
> from
>
>
>
| |
| Alejandro Mesa 2005-01-31, 9:00 pm |
| Then you have to compare to:
select top 1
chartCsiID,
chartXO,
chartCol,
chartLo,
chartHi,
chartMM,
chartYYYY,
chartDOTL,
chartDOTH,
chartBS,
chartLineType
from chart where chartcsiID = 6991 and chartcol = (select max(chartcol) from
chart where chartcsiid = 6991)
go
I think that Kurt have to check both, but when you use TOP and sort in DESC
mode, a sort operation is used and it can be slower than Kurt's statement.
Example:
use northwind
go
select top 1
*
from
[order details]
where
orderid = 11077
and quantity = (select max(quantity) from [order details] where orderid =
11077)
select top 1
*
from
[order details]
where
orderid = 11077
order by
quantity desc
go
AMB
"raydan" wrote:
> See if this helps.
> The difference is that if there are more then 1 row for max(chartcol),
> your query returns all rows, mine will return only one.
>
> select top 1 chartCsiID,
> order by chartcol desc
>
> "Kurt Schroeder" <KurtSchroeder@discussions.microsoft.com> wrote in message
> news:8949B035-FC13-499C-87C3-465E869FD3D0@microsoft.com...
> max
> one?
> from
>
>
>
| |
| raydan 2005-01-31, 9:00 pm |
| You're right and I had thought about it.
Just forgot to add the standard disclaimer to test performance in his
environment.
I would guess that if the quantity of rows per chartcsiID is small,
the "top 1... order by" should perform well.
"Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
news:844886EB-AFDA-45A3-A84A-02E85EDCA9D0@microsoft.com...
> Then you have to compare to:
>
> select top 1
> chartCsiID,
> chartXO,
> chartCol,
> chartLo,
> chartHi,
> chartMM,
> chartYYYY,
> chartDOTL,
> chartDOTH,
> chartBS,
> chartLineType
> from chart where chartcsiID = 6991 and chartcol = (select max(chartcol)
from
> chart where chartcsiid = 6991)
> go
>
> I think that Kurt have to check both, but when you use TOP and sort in
DESC
> mode, a sort operation is used and it can be slower than Kurt's
statement.[color=darkred]
>
> Example:
>
> use northwind
> go
>
> select top 1
> *
> from
> [order details]
> where
> orderid = 11077
> and quantity = (select max(quantity) from [order details] where orderid =
> 11077)
>
> select top 1
> *
> from
> [order details]
> where
> orderid = 11077
> order by
> quantity desc
> go
>
>
> AMB
>
> "raydan" wrote:
>
message[color=darkred]
the[color=darkred]
faster[color=darkred]
max(chartcol)[color=darkred]
| |
| Kurt Schroeder 2005-01-31, 9:00 pm |
| about the missing disclamer: you will all be made to PAY!!! for your omission.
This will create your unending doom!!!
Oh, and,.... the top 1 is much faster!!
thank you
kes
"raydan" wrote:
> You're right and I had thought about it.
> Just forgot to add the standard disclaimer to test performance in his
> environment.
> I would guess that if the quantity of rows per chartcsiID is small,
> the "top 1... order by" should perform well.
>
> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in message
> news:844886EB-AFDA-45A3-A84A-02E85EDCA9D0@microsoft.com...
> from
> DESC
> statement.
> message
> the
> faster
> max(chartcol)
>
>
>
|
|
|
|
|