SQL question 1

[Zargo]

Hi all,

I have a table with the following information:

Mytable
Column1 Column2 Colum3
1000 Userb 2006-10-25
6005 Userc 2006-10-24
3455 Userd 2006-10-24
6005 Usere 2006-10-23
1233 Userf 2006-10-23
6005 Userg 2006-10-22

I want to select the second user who has the value 6005 in columna. So in our example it must be: usere

How can i achieve this?

 

[bborissov]

DECLARE @Test TABLE (Column1 Int, Column2 varchar(20), column3 DateTime)
INSERT INTO @Test VALUES(1000,'Userb','2006-10-25')
INSERT INTO @Test VALUES(6005,'Userc','2006-10-24')
INSERT INTO @Test VALUES(3455,'Userd','2006-10-24')
INSERT INTO @Test VALUES(6005,'Usere','2006-10-23')
INSERT INTO @Test VALUES(1233,'Userf','2006-10-23')
INSERT INTO @Test VALUES(6005,'Userg','2006-10-22')


SELECT TOP 1 *
       FROM (SELECT TOP 2 * FROM @Test WHERE Column1=6005 ORDER BY Column3 DESC) Tbl1
ORDER BY Column3 ASC

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Zargo]

Im getting a error on the last order by clause ORDER BY Column3 ASC. Any idea what can be the cause? The message is invalid column name Column3....

 

[Zargo]

Sorry my mistake, i have solved it thanks for your reply