By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

select unique values

P: 22
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.
Nov 15 '06 #1
Share this Question
Share on Google+
15 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 15 '06 #2

P: 22
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,sheep


thx
Nov 15 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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,sheep


thx
Nov 15 '06 #4

P: 22
very thanks
Nov 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
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 ;)
Nov 15 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 15 '06 #7

PEB
Expert 100+
P: 1,418
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! ;)
Nov 18 '06 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 18 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 18 '06 #10

PEB
Expert 100+
P: 1,418
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! :(
Nov 18 '06 #11

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 18 '06 #12

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

Select distinct(column_name) from table_name;
Nov 24 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
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.CustomerID = Orders.CustomerID
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 GradePointAverage 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 GradePointAverage 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.
Nov 24 '06 #14

P: 1
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.
May 3 '07 #15

P: 6
using the DISTINCT predicate is somewhat expensive, for single tables trying to get distinct values, I guess thats the only way most of us know of at the moment.

but, if u were trying to get unique parent levels (categories) and don't want duplicates there is WHERE EXISTS to the rescue

consider the tables
Categories
CatID
CatName

Products
ProdID
CatID
ProdName

and if u wanted to retrieve any categories that have products within them I would normally have used;
"SELECT DISTINCT CatName FROM Categories INNER JOIN Products ON Categories.CatID = Products.CatID"

and this is where "WHERE EXISTS" can help in performance especially if you've got millions of products...
the new SQL statement would look like;
"SELECT CatName FROM Categories WHERE EXISTS
(
SELECT ProdID FROM Products INNER JOIN Categories ON Products.CatID = Categories.CatID
)"

The difference between the DISTINCT and WHERE EXISTS methods are in the way SQL reads and sorts data.

With DISTINCT; what the query does is to pull out all records within the table and use DISTINCT to filter out any duplicates within the returned set of records

With WHERE EXISTS, the query scans thru the products table and and only brings up unique categoryIDs, therefore the size of the returned set is much smaller to start with
Nov 16 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.