Author Topic: SELECT DISTINCT in UniQuery?  (Read 11638 times)

initself

  • Professional
  • ***
  • Posts: 10
SELECT DISTINCT in UniQuery?
« on: February 15, 2011, 02:57:36 PM »
How do you write a SELECT DISTINCT like statement in UniQuery?

SQL: SELECT DISTINCT(last_update) FROM cust_type;

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Re: SELECT DISTINCT in UniQuery?
« Reply #1 on: February 16, 2011, 06:23:23 AM »
The following is for UniData - not certain for UniVerse.

If you want to display data from a file with unique values, you typically would use:

SORT cust_type BY last_update BREAK-ON last_update (other_fields) DET-SUPP

If you want to get a select list of the unique values, you need to use the SAVING UNIQUE qualifier.  This only works with the UniData ECL Type, so I typically make certain I type the command in lower case from the SB+ TCL prompt.

SELECT cust_type WITH ...
select cust_type SAVING UNIQUE last_update

Note that this creates a sorted list that, depending on what is selected, may included "locators".  For example, if your unique values are A, B and C, your list may be

A^1^1
B^1^1
C^1^2

This shows that A and B were in Attribute 1, value 1, and C was in Attribute 1 value 2. 

If the field you're selecting is the key to another file, you can just select that file to clean up the list.  Otherwise, you should save the list and delete the "locators".

--Tom

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1612
    • Precision Solutions
Re: SELECT DISTINCT in UniQuery?
« Reply #2 on: February 17, 2011, 10:16:17 AM »
+1 to what Tom said.
-Kevin
Accidents "happen"; success, however, is planned and executed.