|
 |
asp_databases thread: Selecting the highest value record
Message #1 by "Tony Yates" <tony.yates@b...> on Thu, 30 Aug 2001 12:57:00
|
|
Hi,
I have a table that looks something like this
DVD
-------
ID
Title
Director
Features
etc
What I want to do is retrieve only the higest record
so if i have 400 dvds i only want to select record 400 or whichever is
highest, however I am not starting at 0 or 1 my records start at some
obscure number and records may be deleted and not replaced so a
recordcount kind of think wont work
Thanks
Tony
Message #2 by "Hasenfratz, Philipp" <maillist@e...> on Thu, 30 Aug 2001 14:41:43 +0200
|
|
> DVD
> -------
> ID
> Title
> Director
> Features
> etc
>
> What I want to do is retrieve only the higest record
>
> so if i have 400 dvds i only want to select record 400 or whichever is
> highest, however I am not starting at 0 or 1 my records start at some
> obscure number and records may be deleted and not replaced so a
> recordcount kind of think wont work
again me...
or try SELECT * FROM DVD as A, DVD as B WHERE A.ID=max(B.ID) GROUP BY A.ID;
but this query I won't do if you don't have to, because this one is not good
regarding performance nor it's nice solved. It's just an idea I had.
---Philipp
Message #3 by "Hasenfratz, Philipp" <maillist@e...> on Thu, 30 Aug 2001 14:37:36 +0200
|
|
> I have a table that looks something like this
>
> DVD
> -------
> ID
> Title
> Director
> Features
> etc
>
> What I want to do is retrieve only the higest record
>
> so if i have 400 dvds i only want to select record 400 or whichever is
> highest, however I am not starting at 0 or 1 my records start at some
> obscure number and records may be deleted and not replaced so a
> recordcount kind of think wont work
Hello Tony,
just run the query :
SELECT max(ID) FROM DVD;
get this value of max(ID) and run
SELECT * FROM DVD WHERE ID= <insert the value of max(ID)>;
or
SELECT * FROM DVD WHERE ID IN (SELECT max(ID) FROM DVD); if your DB supports
sub-queries.
or
SELECT * FROM DVD ORDER BY ID DESC; and read the first record.
---Philipp
Message #4 by "Drew, Ron" <RDrew@B...> on Thu, 30 Aug 2001 09:45:21 -0400
|
|
select max(ID)as highid from dvd
-----Original Message-----
From: Tony Yates [mailto:tony.yates@b...]
Sent: Thursday, August 30, 2001 8:57 AM
To: ASP Databases
Subject: [asp_databases] Selecting the highest value record
Hi,
I have a table that looks something like this
DVD
-------
ID
Title
Director
Features
etc
What I want to do is retrieve only the higest record
so if i have 400 dvds i only want to select record 400 or whichever is
highest, however I am not starting at 0 or 1 my records start at some
obscure number and records may be deleted and not replaced so a
recordcount kind of think wont work
Thanks
Tony
Message #5 by Kyle Burns <kburns@c...> on Thu, 30 Aug 2001 09:11:24 -0500
|
|
This may not be the best, but should work:
SELECT *
FROM dvd
WHERE id IN (SELECT MAX(id) FROM dvd)
Kyle M. Burns, MCSD
ECommerce Technology Manager
-----Original Message-----
From: Tony Yates [mailto:tony.yates@b...]
Sent: Thursday, August 30, 2001 7:57 AM
To: ASP Databases
Subject: [asp_databases] Selecting the highest value record
Hi,
I have a table that looks something like this
DVD
-------
ID
Title
Director
Features
etc
What I want to do is retrieve only the higest record
so if i have 400 dvds i only want to select record 400 or whichever is
highest, however I am not starting at 0 or 1 my records start at some
obscure number and records may be deleted and not replaced so a
recordcount kind of think wont work
Thanks
Tony
|
|
|
View Cart
