select unique values

[Jahangir]

Dear Fellows,
I want to extract unique values from a table's field. That is values which repeated in table does not repeat in query result.

Thx.

[NeoPa]

You need to decide what level of uniqueness you require.
In design view of the query, select query properties.
Two of the properties are 'Unique Values' & 'Unique Records' - decide which one you need and select just one.
In SQL you add one of the predicates 'DISTINCT' or 'DISTINCTROW' after the word SELECT.

[Jahangir]

Explaination is here



In my table a lot of animals are addedd Cat, dog, cow, cat, cat,sheep, cow, sheep, etc

Thses animals are repeated in ID fashion.

I want to see what animals are there in table.
i.e. return me animal only ones


result of Cat, dog, cow, cat, cat,sheep, cow, sheep of this data should be

cat,dog,cow,she ep


thx

[MMcCarthy]

Something like

SELECT AnimalID
FROM TableName
GROUP BY AnimalID;

Or

SELECT DISTINCT AnimalID
FROM TableName;

Either should work.

Explaination is here



In my table a lot of animals are addedd Cat, dog, cow, cat, cat,sheep, cow, sheep, etc

Thses animals are repeated in ID fashion.

I want to see what animals are there in table.
i.e. return me animal only ones


result of Cat, dog, cow, cat, cat,sheep, cow, sheep of this data should be

cat,dog,cow,she ep


thx

[Jahangir]

very thanks

[NeoPa]

Something like

SELECT AnimalID
FROM TableName
GROUP BY AnimalID;

Or

SELECT DISTINCT AnimalID
FROM TableName;

Either should work.

The second version is definitely the 'correct' one for this problem though.
Ask Killer what the difference in performance would be if processed on one of his mega-tables ;)

[MMcCarthy]

The second version is definitely the 'correct' one for this problem though.
Ask Killer what the difference in performance would be if processed on one of his mega-tables ;)

Thank goodness most of us don't come up against that problem too often.

[PEB]

Thank goodness most of us don't come up against that problem too often.

It's comming soon!
Everyone at his turn! But it's good to have ideas how you can optimize the SQL for better performance!

Clues like this aren't to be ommited!

Espesially in my situation ... Approaching the Killers'one! ;)

[NeoPa]

Thank goodness most of us don't come up against that problem too often.

Amen to that Mary.
Although I do keep transaction data at work of about 35,000 transaction lines per month for a period of 7 years. That's around 3 million transaction lines.
When you consider that they're nearly always accessed via a query linking in the transaction headers (average 3 or 4 lines per transaction - so around a million headers).
I use MS SQL Server to handle all that though.
I have to say it manages it very well.
I do run into performance problems from time-to-time though.

[MMcCarthy]

Amen to that Mary.
Although I do keep transaction data at work of about 35,000 transaction lines per month for a period of 7 years. That's around 3 million transaction lines.
When you consider that they're nearly always accessed via a query linking in the transaction headers (average 3 or 4 lines per transaction - so around a million headers).
I use MS SQL Server to handle all that though.
I have to say it manages it very well.
I do run into performance problems from time-to-time though.

My solution is simpler, I have a system at the moment that has a very a very active audit trail required by the clients IT dept. I have a trigger that dumps the data out to a fixed length text file everytime the record count goes over 10,000. The file is timestamped and dumped to a specific folder. Because this dump is based on a standard schema it can be imported into any application by IT for analysis as required.

I've always felt that this is the best solution to large transaction files because the if the dump is in a fixed length text format I've yet to come across any application that can't handle it. Access also allows you to create export specifications that can be repeatedly used in these dumps, one of the features I love about this is you can convert all exporting values to text and therefore not incur any formating problems and you can eliminate the quotes around text. It can be manipulated to mirror the requirements of the system you want to use for reporting.

I often use another access application. As this data imports very easily into access.

Just wondering now if this would be useful for Killers other problem.

I have some text file read and write code if you want it killer.

Mary

[PEB]

And my solution is to keep each year in separate Access database / databases.

In the new database for the new year is transmitted the needed information from the last year.

Usually i don't do changes to my databases so it is easy to treat the tables...

But my problem is with the differents ID's for each year... The same ID can handle to different values...

And here is the biggest dificulty!

But for the moment never wanted to join the different periods and use the IDs

I should think about the join of databases and the different IDs! :(

[NeoPa]

I (we) need access to any of the data at short notice so archiving isn't an option.
Not a problem - SQL Server handles the volumes fine.
I wouldn't split it by year as that just complicates accessing the data. Obviously I would try out these techniques if the data weren't already adequately handled.

[tomarvijay80]

U can select unique value from any column by using this query.

Select distinct(column _name) from table_name;

[NeoPa]

That's nearly right.
DISTINCT & DISTINCTROW are predicates applied to the SQL SELECT command.
They do not require parentheses () as they are not functions.

ALL, DISTINCT, DISTINCTROW, TOP Predicates
Specifies records selected with SQL queries.

Syntax
SELECT [ALL | DISTINCT | DISTINCTROW | [TOP n [PERCENT]]]
FROM table

A SELECT statement containing these predicates has the following parts:

Part Description
ALL Assumed if you do not include one of the predicates. The Microsoft Jet database engine selects all of the records that meet the conditions in the SQL statement. The following two examples are equivalent and return all records from the Employees table:
SELECT ALL *
FROM Employees
ORDER BY EmployeeID;

SELECT *
FROM Employees
ORDER BY EmployeeID;

DISTINCT Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT statement must be unique. For example, several employees listed in an Employees table may have the same last name. If two records contain Smith in the LastName field, the following SQL statement returns only one record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;

If you omit DISTINCT, this query returns both Smith records.

If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

The output of a query that uses DISTINCT is not updatable and does not reflect subsequent changes made by other users.

DISTINCTROW Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders:
SELECT DISTINCTROW CompanyName
FROM Customers INNER JOIN Orders
ON Customers.Custo merID = Orders.Customer ID
ORDER BY CompanyName;

If you omit DISTINCTROW, this query produces multiple rows for each company that has more than one order.

DISTINCTROW has an effect only when you select fields from some, but not all, of the tables used in the query. DISTINCTROW is ignored if your query includes only one table, or if you output fields from all tables.

TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAvera ge DESC;

If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.

The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose that, instead of the top 25 students, you want the bottom 10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAvera ge ASC;

The ASC predicate specifies a return of bottom values. The value that follows TOP must be an unsigned Integer.

TOP does not affect whether or not the query is updatable.

table The name of the table from which records are retrieved.

[rozenchv]

What if the roster had several classes and you wanted to pick out top 1% from each class based on grades, how would you do that?

Thanks in advance.