BBS.PROGENIC.COM / Chat / Coding / SQL Select Help... / Latest Posts

RE: SQL Select Help...

h4ck-y0u — Sat, 21 Jun 2008 21:38:17 GMT

You could also use between. Something along the lines of

SELECT TOP 11 * FROM (SELECT row_number() OVER (ORDER BY id desc) AS rownum, column1, column2, etc FROM tablename) AS A WHERE A.rownum BETWEEN (2) AND (11)

RE: SQL Select Help...

Pen — Sun, 08 Jun 2008 18:43:56 GMT

proge (6/8/2008)

try

select top 10 * from tablename where id not in (select top 1 id from tablename order by id desc) order by id desc

Thats the one Thanks to everyone who helped

RE: SQL Select Help...

proge — Sun, 08 Jun 2008 15:17:22 GMT

try

select top 10 * from tablename where id not in (select top 1 id from tablename order by id desc) order by id desc

RE: SQL Select Help...

Pen — Sun, 08 Jun 2008 14:32:43 GMT

Now im more confused, using GBrains suggestion

"select top 10 * from tablename where id not in (select top 1 id from tablename) order by id desc" - I just get the 10 most recent entries, so its not skipping the 1 very most recent.

But, if I take out the descend part...
"select top 10 * from tablename where id not in (select top 1 id from tablename) order by id" - then this will work, it takes the oldest entries and does skip the first record.

... this doesnt make sense to me but im sure if you test it you'll get the same.

So it kinda works, just gets the oldest data instead of the newest - any ideas?

RE: SQL Select Help...

Pen — Sun, 08 Jun 2008 14:28:23 GMT

proge (6/8/2008)

the example 1 in the sql gbrain posted should work, can u post what you tried to use?

if you want to do it like anon said i can help, what code are you using to load the data initially?

Hey matey,

Just tried GBrains again, the first one like you said - its just selecting the top ten, its not skipping one. At the moment Ive not put it into .NET but it'll be a case of loading it into a DataList.

At the moment im just using Query Analyzer with the data til I get the Select statement working.

RE: SQL Select Help...

proge — Sun, 08 Jun 2008 09:47:37 GMT

the example 1 in the sql gbrain posted should work, can u post what you tried to use?

if you want to do it like anon said i can help, what code are you using to load the data initially?

RE: SQL Select Help...

G-Brain — Sun, 08 Jun 2008 07:42:55 GMT

Pen (6/8/2008)

thats what I wanna know how to do

Well, the SQL would just be 'select top 11 * from tablename order by id desc', the result of that would be stored in some kind of array. Getting rid of the first element of an array is called "shifting", how to do it in the language you're using shouldn't be hard to find. The function is probably called "Shift()" or something.

Good luck!

RE: SQL Select Help...

Pen — Sun, 08 Jun 2008 07:36:47 GMT

Anonymoose - thats what I wanna know how to do

GBrain - Thanks for searching matey, I found those examples aswell - The first didnt seem to work if the column held anything other that integer data, and the second I think was for MySQL.

RE: SQL Select Help...

G-Brain — Sun, 08 Jun 2008 05:39:35 GMT

Google gives me this:

1

select top 10 * from tablename where id not in (select top 1 id from tablename) order by id desc

select * from tablename order by id desc limit 1,10

The top 1 part could probably be done better and the other method looks kinda slow, give it a try though. Anonymoose's suggestion is probably the fastest.

RE: SQL Select Help...

anonymoose — Sun, 08 Jun 2008 05:31:44 GMT

Could you not do Top 11 instead and skip the first result returned? Or is that what you were asking how to do... Sorry, it's early!

SQL Select Help...

Pen — Sun, 08 Jun 2008 02:35:01 GMT

I have a database that is constantly being added to... what im trying to do is select the 10 most recent entries EXCEPT for the very most recent.

Selecting the 10 most recent is something I can do.... 'select top 10 * from tablename order by id desc' - but how do I leave out the most recent?

SQL Server 2005.