SQL select statement

[kennyaidan]

Hi ,
i am a beginner sql programmer and i am having trouble with a particular sql select statement that i want to execute. I want to select the first row's values based upon id value after i do a select all statement order by desc. The select all code if have is

"select * from story1 order by id desc"

The story1 table looks like the following
id | name | age |
01 Aidan 18
02 Rob 19

The select all statement works fine, however i can't get it to output the first row only ie "01 Aidan 18"
Does anyone know code that could help me
thanks
aidan

 

[LFCfan]

select TOP 1 * from story1 order by id desc

HTH
LFCfan

 

[kennyaidan]

thanks alot for that great help. just one more beginner question how would i select the second row after "order by id desc". For example if the story1 table looked like the following

id | name | age |
01 Aidan 18
02 Rob 19
03 Damo 20

how would i select the second row ie "02 Rob 19" would it be

select TOP 1-1* from story1 order by id desc
thanks
aidan

 

[swampBoogie]

To get the row with the second highest id

select top 1 * from story1
 where id not in (select max(id) from story1)
order by id desc

 

[kennyaidan]

Thanks a lot again guys for your help but is there no way to move down through the table after the "order by id desc" command. What i am trying to say is, if the table was as follows

id | name | age |
01 Aidan 18
02 Rob 19
03 Damo 20
04 simon 19
05 phil 20

and i wanted to get row fours details, ie "04 simon 19". Is there a "first row - 3" command to retreive the values in row four.
thanks
aidan

 

[JamesLean]

SELECT TOP n *
FROM story1
WHERE id NOT IN (
  SELECT TOP n-1 *
  FROM story1
  ORDER BY id DESC
)
ORDER BY id DESC

--James

 

[kennyaidan]

thanks james but i still can't get it to work.

I entered the code as follows

select top 3 * from story1
where id not in (select TOP 3-1 * from story1 ORDER BY id DESC)
order by id desc

i get an error message back stating
Incorrect syntax near the keyword 'from'.

So when i replace * with name, it doesn't give me an error message but i get the following back
Name
Aidan
Rob
Damo

I was hoping to get just the following back
Name
Rob
Damo

Am i using your code the wrong way??, any help greatly appreicated
thanks
aidan

 

[Kudel]

Hi

I think this is right

SELECT TOP 1 *
FROM story1
WHERE id NOT IN (
SELECT TOP n-1 id
FROM story1
ORDER BY id DESC
)
ORDER BY id DESC

"select top n-1" does, for some reason, not work.
If you use n = 4, you can't put 4-1 you have to put 3.

-Kudel

 

[JamesLean]

Yes, I was just giving the general syntax. You have to specify the exact value after TOP - you can't use an expression like 3-1. --James

 

[kennyaidan]

thanks Kudel, that seems to have solved the problem, thanks everyone for the help
aidan