Cursor to return the first line of a table

Cursor to return the first line of a table

Post by Franck Jobar » Fri, 23 May 2003 15:07:36



Hello,

I am trying to return a string that contains the first line of a table

It must be something like
field1 : Value1
field2 : Value2
...
fieldn : Valuen

I do the following code but i can't return the Value (i also try with 2
cursors without success)



'
DECLARE tnames_cursor CURSOR
FOR
    SELECT Column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'DROIT'
OPEN tnames_cursor




BEGIN

    BEGIN





    END

END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

Thanks for your helf

Franck

 
 
 

Cursor to return the first line of a table

Post by Vinod » Fri, 23 May 2003 15:13:21


Use D-SQL when you have to get the Top 1 row ...
Dynamic SQL - http://www.algonet.se/~sommar/dynamic_sql.html

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> Hello,

> I am trying to return a string that contains the first line of a table

> It must be something like
> field1 : Value1
> field2 : Value2
> ...
> fieldn : Valuen

> I do the following code but i can't return the Value (i also try with 2
> cursors without success)



> '
> DECLARE tnames_cursor CURSOR
> FOR
>     SELECT Column_name
>     FROM INFORMATION_SCHEMA.COLUMNS
>     WHERE TABLE_NAME = 'DROIT'
> OPEN tnames_cursor




> BEGIN

>     BEGIN





>     END

> END
> CLOSE tnames_cursor
> DEALLOCATE tnames_cursor

> Thanks for your helf

> Franck


 
 
 

Cursor to return the first line of a table

Post by David Porta » Fri, 23 May 2003 15:21:27


You must use ORDER BY with TOP otherwise you can't expect consistent
results. Remember there is not first row unless you define it with ORDER BY.

Use UNION to avoid the need for a cursor:

CREATE TABLE Droit (col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER
/* Primary key not specified */ )

INSERT INTO DROIT VALUES (1,2,3,4)

SELECT 'COL1',col1 FROM
 (SELECT TOP 1 *
  FROM Droit
  ORDER BY col1) c
UNION ALL
SELECT 'COL2',col2 FROM
 (SELECT TOP 1 *
  FROM Droit
  ORDER BY col1) c
UNION ALL
SELECT 'COL3',col3 FROM
 (SELECT TOP 1 *
  FROM Droit
  ORDER BY col1) c
UNION ALL
SELECT 'COL4',col4 FROM
 (SELECT TOP 1 *
  FROM Droit
  ORDER BY col1) c

If you have a single column PK then you could avoid TOP with:
WHERE pkcol = (SELECT MIN(pkcol) FROM Droit)

Please include a CREATE TABLE statement and sample data as INSERT statements
with future posts.

--
David Portas
------------
Please reply only to the newsgroup
--


> Hello,

> I am trying to return a string that contains the first line of a table

> It must be something like
> field1 : Value1
> field2 : Value2
> ...
> fieldn : Valuen

> I do the following code but i can't return the Value (i also try with 2
> cursors without success)



> '
> DECLARE tnames_cursor CURSOR
> FOR
>     SELECT Column_name
>     FROM INFORMATION_SCHEMA.COLUMNS
>     WHERE TABLE_NAME = 'DROIT'
> OPEN tnames_cursor




> BEGIN

>     BEGIN



DROIT)



>     END

> END
> CLOSE tnames_cursor
> DEALLOCATE tnames_cursor

> Thanks for your helf

> Franck

 
 
 

Cursor to return the first line of a table

Post by Franck Jobar » Fri, 23 May 2003 15:28:13


I tried with EXEC. No more success !! Maybe because i am in the FETCH of a

Franck



Use D-SQL when you have to get the Top 1 row ...
Dynamic SQL - http://www.algonet.se/~sommar/dynamic_sql.html

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> Hello,

> I am trying to return a string that contains the first line of a table

> It must be something like
> field1 : Value1
> field2 : Value2
> ...
> fieldn : Valuen

> I do the following code but i can't return the Value (i also try with 2
> cursors without success)



> '
> DECLARE tnames_cursor CURSOR
> FOR
>     SELECT Column_name
>     FROM INFORMATION_SCHEMA.COLUMNS
>     WHERE TABLE_NAME = 'DROIT'
> OPEN tnames_cursor




> BEGIN

>     BEGIN



DROIT)



>     END

> END
> CLOSE tnames_cursor
> DEALLOCATE tnames_cursor

> Thanks for your helf

> Franck

 
 
 

Cursor to return the first line of a table

Post by Vinod » Fri, 23 May 2003 15:38:20


Iam not sure if this is the requirement you have ...

Use NorthWind
GO



DECLARE tnames_cursor CURSOR
FOR
    SELECT Column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'employees' and Data_Type NOT LIKE 'Image'
OPEN tnames_cursor




BEGIN

    BEGIN






    END

END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> I tried with EXEC. No more success !! Maybe because i am in the FETCH of a
> CURSOR ?

> Franck



> Use D-SQL when you have to get the Top 1 row ...
> Dynamic SQL - http://www.algonet.se/~sommar/dynamic_sql.html

> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> SCT Software Solutions



> > Hello,

> > I am trying to return a string that contains the first line of a table

> > It must be something like
> > field1 : Value1
> > field2 : Value2
> > ...
> > fieldn : Valuen

> > I do the following code but i can't return the Value (i also try with 2
> > cursors without success)



> > '
> > DECLARE tnames_cursor CURSOR
> > FOR
> >     SELECT Column_name
> >     FROM INFORMATION_SCHEMA.COLUMNS
> >     WHERE TABLE_NAME = 'DROIT'
> > OPEN tnames_cursor




> > BEGIN

> >     BEGIN



> DROIT)



> >     END

> > END
> > CLOSE tnames_cursor
> > DEALLOCATE tnames_cursor

> > Thanks for your helf

> > Franck

 
 
 

Cursor to return the first line of a table

Post by Ben Dilt » Fri, 23 May 2003 16:18:59


Some pseudocode:




(idcol)

>-----Original Message-----
>Hello,

>I am trying to return a string that contains the first
line of a table

>It must be something like
>field1 : Value1
>field2 : Value2
>....
>fieldn : Valuen

>I do the following code but i can't return the Value (i
also try with 2
>cursors without success)



>'
>DECLARE tnames_cursor CURSOR
>FOR
>    SELECT Column_name
>    FROM INFORMATION_SCHEMA.COLUMNS
>    WHERE TABLE_NAME = 'DROIT'
>OPEN tnames_cursor




>BEGIN

>    BEGIN





- Show quoted text -



>    END

>END
>CLOSE tnames_cursor
>DEALLOCATE tnames_cursor

>Thanks for your helf

>Franck

>.

 
 
 

Cursor to return the first line of a table

Post by Franck Jobar » Fri, 23 May 2003 16:53:57


Thanks a lot for this code. It is exactly that i am searching.

Franck



Iam not sure if this is the requirement you have ...

Use NorthWind
GO



DECLARE tnames_cursor CURSOR
FOR
    SELECT Column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'employees' and Data_Type NOT LIKE 'Image'
OPEN tnames_cursor




BEGIN

    BEGIN






    END

END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> I tried with EXEC. No more success !! Maybe because i am in the FETCH of a
> CURSOR ?

> Franck



> Use D-SQL when you have to get the Top 1 row ...
> Dynamic SQL - http://www.algonet.se/~sommar/dynamic_sql.html

> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> SCT Software Solutions



> > Hello,

> > I am trying to return a string that contains the first line of a table

> > It must be something like
> > field1 : Value1
> > field2 : Value2
> > ...
> > fieldn : Valuen

> > I do the following code but i can't return the Value (i also try with 2
> > cursors without success)



> > '
> > DECLARE tnames_cursor CURSOR
> > FOR
> >     SELECT Column_name
> >     FROM INFORMATION_SCHEMA.COLUMNS
> >     WHERE TABLE_NAME = 'DROIT'
> > OPEN tnames_cursor




> > BEGIN

> >     BEGIN



> DROIT)



> >     END

> > END
> > CLOSE tnames_cursor
> > DEALLOCATE tnames_cursor

> > Thanks for your helf

> > Franck

 
 
 

Cursor to return the first line of a table

Post by Franck Jobar » Fri, 23 May 2003 17:10:52


Arghhh, a little more problem. This code does not seems to work with
inserted and deleted in a trigger

Franck



Iam not sure if this is the requirement you have ...

Use NorthWind
GO



DECLARE tnames_cursor CURSOR
FOR
    SELECT Column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'employees' and Data_Type NOT LIKE 'Image'
OPEN tnames_cursor




BEGIN

    BEGIN






    END

END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
SCT Software Solutions


> I tried with EXEC. No more success !! Maybe because i am in the FETCH of a
> CURSOR ?

> Franck



> Use D-SQL when you have to get the Top 1 row ...
> Dynamic SQL - http://www.algonet.se/~sommar/dynamic_sql.html

> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD
> SCT Software Solutions



> > Hello,

> > I am trying to return a string that contains the first line of a table

> > It must be something like
> > field1 : Value1
> > field2 : Value2
> > ...
> > fieldn : Valuen

> > I do the following code but i can't return the Value (i also try with 2
> > cursors without success)



> > '
> > DECLARE tnames_cursor CURSOR
> > FOR
> >     SELECT Column_name
> >     FROM INFORMATION_SCHEMA.COLUMNS
> >     WHERE TABLE_NAME = 'DROIT'
> > OPEN tnames_cursor




> > BEGIN

> >     BEGIN



> DROIT)



> >     END

> > END
> > CLOSE tnames_cursor
> > DEALLOCATE tnames_cursor

> > Thanks for your helf

> > Franck

 
 
 

1. returning the first 200 records - cursor?

I need to use MS Sql to return the first 200 records from a table containing
1000 records.

The best that I have come up with so far is to reverse-sort the table into a
forward-only recordset; FETCH Absolute 800 and return the cursor - which
should contain the 200 records I am looking for.

This must be a very common problem with a simple solution but I have not
been able to find that in MSDN, my books, or this newsgroup.

Thanks in advance.

2. unique constraint error for ADO

3. Fetch from cursor only returns the first row

4. Fetching binary data

5. cursor to delete first ten rows of table

6. Problem with VB6 and access2000 program in XP

7. need to return the first 200 records of a table

8. How do you refresh a DBGrid?

9. Query question - INNER JOIN returning only first record from second table

10. Returning First or One Record of a Table

11. Help Please - .Find method will not return the first value in a table

12. sql return only first matching record from join table

13. Query of two tables, Return the first if not in second