How to best select just the first record?

[gorilla1]

The code below loops through the records and writes out all the records meeting the criteria. How can I most easily select and write out only the very first record (and most recent one) that meets the criteria?

sqlh = "SELECT * FROM items WHERE itemCrit1='" & condition1 & "' And itemCrit2 ='xyz' And itemStatus='A' ORDER BY itemTime DESC"

set addrsh = conn.execute(sqlh)

do while not addrsh.eof

Response.Write(addrsh(1) )

addrsh.movenext

loop

G

[Spookster]

Just use sql to limit the number of records returned. Assuming your order by is putting the most recent record at the top then:

sqlh = "SELECT TOP 1 * FROM items WHERE itemCrit1='" & condition1 & "' And itemCrit2 ='xyz' And itemStatus='A' ORDER BY itemTime DESC"

[gorilla1]

Spookster,
Ah, thanks - that does it. Was not aware of that.
G

[Caffeine]

If you were using an Oracle databse that snippet would not work, you would have to use something like this:
select * from items where rownum=1 order by itemTime desc;

-phleg-

[Spookster]

I guess I assumed he was using msSQL or Access as is normally the case with using ASP. :) And that is correct for oracle, I use that at work all the time. If he were using MySQL then you would use the LIMIT clause

SELECT * FROM tablename [where and order by stuff] LIMIT 1

[Caffeine]

Originally posted by Spookster
And that is correct for oracle, I use that at work all the time. If he were using MySQL then you would use the LIMIT clause


Yep, I use it all the time too.:)

It bugs my though that MS, mySQL and Oracle all have their own way of doing the same thing. As a developer, it would be so much easier if they could all follow one standard.

Before I got this job where I am right now, I had only worked with MS SQL Server and Access databases, here we use Oracle. Needless to say, it took a while to get used to the 'new' SQL-queries.

-phleg-

[BigDaddy]

phlegmatic, thanks. I used to use ASP/SQL Server, but now use it with Oracle. I wondered how to only get the first record. :)

[whammy]

If you're using SQL Server, a better way is to use "@@IDENTITY" or to use a trigger to return the last inserted "id".

I don't have the code handy on this computer, but if you need it lemme know. :)

[BigDaddy]

I'd kinda like to see that, Whammy.

[whammy]

Cool, I will give you a couple of examples, also using a trigger. I don't think you can use that with Oracle, though. ;) (I could be wrong)

[BigDaddy]

Probably not, but I am doing a bit of personal stuff using SQL Server.

Besides, it'd just be kinda cool to see it.

You doing a lot of the database stuff now that you're a top dog around the place? I know you were doing some...

[whammy]

Yeah, a bit more. Dang, I was too busy today to think about this, pm me during the day and remind me!!!

[Caffeine]

The @@IDENTITY thing will most likely not work in Oracle since it's a Microsoft thing[i think] and to be completely honest I have not spent enough of time with Oracle to know what the equivalent is.

One could do the same with a trigger but I'll find out the equivalent, hold on a moment ...

[pause-music]

-phleg-

[Caffeine]

Hmm.. I still have not found out what Oracle's equivalent to @@IDENTITY is, maybe there is none.

Someone please correct me if I'm wrong.

-phleg-