need a sql query as unique without sorting

[PeriyurParthi]

i need the SQL Query for the following

i have 2 fields no(integer) and name(text)
i have following records

5 john
1 michale
4 christoper
2 armstrong
3 bosco
5 john
4 christoper

what i need is unique datas without sorting in asc or desc, i e original order. strictly should not be sorted but datas should be uniquely required.
thanks
parthi

 

[jhall156]

have you tried

SELECT DISTINCT no, name FROM table;
JHall

 

[rgbean]

While the simple SELECT may work, technically you can't guarentee any given order you don't specify. If the table has an increasing Primary key, you could use it, or if you are in FoxPro and are only working with a single table, then the following should work.

SELECT DISTINCT no, name, recno() FROM table ;
ORDER by 3 ;

Rick

 

[JARVIMA]

Rick,

I had problems with your select statement. VFP only picks up the 1st recno and displays the same recno for each record in the results.

Do you find the same ?

rgds/Martin.

 

[rgbean]

Martin,
While you can get strange results if you work with multiple tables, or you if you try to specify recno("table&quot [i.e. with a specific alias/workarea], I never found it to fail with just a single table. (At least not in 5.0 SP3, 6.0 SP5, 7.0 SP1.)

Rick

 

[PeriyurParthi]

i tried with this
Select distinct no, name from table

but it is sorting in ascending, what actually i need , i need a query to display unqiue values without sorting..
iam trying to do in acess,
thanks
parthi

 

[jhall156]

If it's important to you to have your output unsorted then what you really want is it sorted by the order in which the records were entered. The easiest way to accomplish this is to create a surrogate autonumber key. Then order by that key in your query. JHall

 

[rgbean]

Parthi,
VFP's SQL Select, like all other versions that I'm aware of, is free to present the data in any order it chooses, UNLESS you supply an ORDER BY clause.

Rick

 

[chpicker]

Rick is right. There is absolutely NO guarantee whatsoever as to the order of the results of an SQL query unless you use an ORDER BY clause.

There are 2 solutions I can think of. One is to have a field for the record number in it. The other is to scrap SQL and write a small UDF to generate the table. Here's an example:
[tt]
CREATE CURSOR myTest ;
(no i(1),name c(10))
SELECT SourceTable
SET ORDER TO
GOTO TOP
SCAN
SCATTER FIELDS no,name TO aRec
SELECT myTest
LOCATE FOR no=aRec[1] AND name=aRec[2]
IF !FOUND()
INSERT INTO myTest FROM ARRAY aRec
ENDIF
SELECT SourceTable
ENDSCAN
[/tt]
Ian